请教一条sql查询语句

Question

有下面两个表

desk表结构
desk_id     left_id     right_id
1            3           null
2           null          2
3            1            2

user表结构
user_id    name
1          小明
2          小红
3.         小丽

left_id 与 user_id 对应

想得到这样一个结果, 把left_id 替换成 white, right_id 替换成 black

desk_id      color     name
1            white      小丽
2.           black      小红 
3            white      小明
3.           black      小红

我的做法是两条sql语句查询, 然后再把结果合并在一起

select desk_id, white as color, name from desk inner join user on user_id = left_id where left_id is not null;

select desk_id, black as color, name from desk inner join user on user_id = left_id where right_id is not null;

能不能只用一条sql语句得到呢 , 或者还有其它更好的办法 ?

Answer 1

两个问题。

第一，你需要笛卡尔积join，才能在两个3行的表取出3行以上的记录

第二，你需要IS NULL的为A，IS NOT NULL的为B，所以需要if语句，在mysql里是CASE语法。

方便各位调试

CREATE TABLE `user` (
  `user_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(8) COLLATE utf8_unicode_ci DEFAULT '',
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `desk` (
  `desk_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `left_id` int(10) unsigned DEFAULT NULL,
  `right_id` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`desk_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO test.user (name) VALUES ('小明');
INSERT INTO test.user (name) VALUES ('小红');
INSERT INTO test.user (name) VALUES ('小丽');

INSERT INTO test.desk (left_id, right_id) VALUES (3, null);
INSERT INTO test.desk (left_id, right_id) VALUES (null, 2);
INSERT INTO test.desk (left_id, right_id) VALUES (1, 2);

体会一下楼主的脑洞

最终还是成功了

调试了60次。。

Answer 2

题目我没细看,如果你的两条select正确,最简单的做法就是,把你上面的两条已经写好的select语句,用union all连接.查一下union all的用法,你就清楚了

Answer 3

最终结果的骨架相当于一次inner join：

test=# select * from desk d inner join "user" u
test-#   on d.left_id=u.user_id or d.right_id=u.user_id;

 desk_id | left_id | right_id | user_id | name
---------+---------+----------+---------+------
       1 |       3 |          |       3 | 小丽
       2 |         |        2 |       2 | 小红
       3 |       1 |        2 |       1 | 小明
       3 |       1 |        2 |       2 | 小红
(4 行记录)

我们只要把left_id、right_id、user_id的各种组合映射到color就行了。也即当user_id = left_id时color=white，否则color=black：

test=# select desk_id, name,
test-#   case when user_id = left_id then 'white' else 'black'
test-#     end as color
test-# from desk d inner join "user" u
test-#   on d.left_id=u.user_id or d.right_id=u.user_id;

 desk_id | name | color
---------+------+-------
       1 | 小丽 | white
       2 | 小红 | black
       3 | 小明 | white
       3 | 小红 | black
(4 行记录)

这里假定left和right不会是同一个人（否则结果中只有一行）。