我必须从表user
中获取9列,比如(a,b,c,d,e,f,g,h,I),其中id=id,从同一个表user
中获取2列,比如来自管理员id的(j,k)。这可能吗?
我尝试了UNION
,但它给了我一个错误,说列应该是相同的。
我也试过:
SELECT a,b,c,d,e,f,g,h,i from user where id=id JOIN select j, k from id=admin_id limit 1
但其中任何一项都不起作用。我怎样才能做到这一点?
编辑:@Cetin Basoz回答了我的疑问。
您可以为缺少的列设置常数值。即:
SELECT a,b,c,d,e,f,g,h,i from user where id=someid
union
select j, k, null, null, null, null, null, null, null from user
where id=admin_id;
编辑:按建议工作:
DBFiddle演示
你的问题还不清楚,看起来你是在追求一个工会,但也许你想说你想收回一些东西,比如:
a、 b,c,d,e,f,g,h,i,j,k?如果是,那么:
select * from
(SELECT a,b,c,d,e,f,g,h,i
from _user where id=someId) t1
cross join
(SELECT j,k
from _user where id=admin_id) t2;
SELECT MAX(CASE WHEN id = @some_user_id THEN a END) AS a,
MAX(CASE WHEN id = @some_user_id THEN b END) AS b,
-- ...
MAX(CASE WHEN id = @some_user_id THEN i END) AS i,
MAX(CASE WHEN id = @admin_id THEN j END) AS j,
MAX(CASE WHEN id = @admin_id THEN k END) AS k
FROM source_table
如果指定的@user_id
不存在,则根据列将生成NULL。
如果表为空,那么输出也将为空。
有没有更短的方法naive_user
SELECT MAX(a) AS a, MAX(b) AS b, ..., MAX(k) AS k
FROM ( SELECT a,b,c,d,e,f,g,h,i,NULL AS j,NULL AS k
FROM sourcetable
WHERE id = @some_user_id
UNION ALL
SELECT NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,j,k
FROM sourcetable
WHERE id = @admin_id ) AS subquery