MSSQL 比较SQL难的问题？

[hwj383]

问题描述:
两张表A,B

A表结构
ID，列1，列2

B表结构
ID, 列1，列2

其中B中表结构列1存的是A中的ID

具体数据如下
A
ID 列1 列2
1 1 1
2 2 2
3 3 3
4 4 4
5 5 5

B具体数据如下
ID 列1 列2
1 1 1
2 2 1
3 3 1
4 1 2
5 2 2

现在想实现如下查询
A中的所有记录全选,并增加一列C
C的结果是如果从B表中查出（B表列2做条件），如果有列1数据则C为true,否则为false

--如查询B表列2 = 1 的情况，结果如下

ID 列1 列2 C
1 1 1 true
2 2 2 true
3 3 3 true
4 4 4 false
5 5 5 false

简单就是A表中的所有列都要查询出来，但是C列根据B表中的条件决定是什么值。

[rucypli]

select a.*,case when b.列2 is null then 'trun' else 'false' end as C
from a left join b on a.id=B.列2

[SQL77]

SELECT A.*,CASE WHEN EXISTS(SELECT 1 FROM B WHERE A.ID=B.列1) THEN 'TRUE' ELSE 'FALSE' END AS 'C'
FROM A

[moonshineidolon]

select a.*,

case when b.列2 is null then 'trun' else 'false' end as C

from a
left join b on a.id=B.列2

[csdyyr]

DECLARE @TA TABLE([ID] INT, [列1] INT, [列2] INT)
INSERT @TA 
SELECT 1, 1, 1 UNION ALL 
SELECT 2, 2, 2 UNION ALL 
SELECT 3, 3, 3 UNION ALL 
SELECT 4, 4, 4 UNION ALL 
SELECT 5, 5, 5

DECLARE @TB TABLE([ID] INT, [列1] INT, [列2] INT)
INSERT @TB 
SELECT 1, 1, 1 UNION ALL 
SELECT 2, 2, 1 UNION ALL 
SELECT 3, 3, 1 UNION ALL 
SELECT 4, 1, 2 UNION ALL 
SELECT 5, 2, 2

SELECT A.*,CASE WHEN A.[列1]=B.[列1] THEN 'true' ELSE 'false' END AS C
FROM @TA AS A LEFT JOIN (SELECT * FROM @TB WHERE 列2=1) AS B 
  ON A.[列1]=B.[列1]
/*
ID          列1          列2          C
----------- ----------- ----------- -----
1           1           1           true
2           2           2           true
3           3           3           true
4           4           4           false
5           5           5           false
*/

[xiequan2]

DECLARE @TA TABLE([ID] INT, [列1] INT, [列2] INT)
INSERT @TA 
SELECT 1, 1, 1 UNION ALL 
SELECT 2, 2, 2 UNION ALL 
SELECT 3, 3, 3 UNION ALL 
SELECT 4, 4, 4 UNION ALL 
SELECT 5, 5, 5

DECLARE @TB TABLE([ID] INT, [列1] INT, [列2] INT)
INSERT @TB 
SELECT 1, 1, 1 UNION ALL 
SELECT 2, 2, 1 UNION ALL 
SELECT 3, 3, 1 UNION ALL 
SELECT 4, 1, 2 UNION ALL 
SELECT 5, 2, 2



declare @a int
set @a=1
select A.ID,列1,列2,c from @TA A, (select row_number() over (order by getdate()) id,case when 列2=@a then 'true' else 'false' end c from @tb) tmp where A.id=tmp.id


ID          列1          列2          c
----------- ----------- ----------- -----
1           1           1           true
2           2           2           true
3           3           3           true
4           4           4           false
5           5           5           false

(5 行受影响)

[feixianxxx]

[Quote=引用 2 楼 SQL77 的回复:]
SQL code
SELECT A.*,CASE WHEN EXISTS(SELECT 1 FROM B WHERE A.ID=B.列1) THEN 'TRUE' ELSE 'FALSE' END AS 'C'
FROM A
[/Quote]
UP

[jinlingoo1]

up

[htl258_Tony]

[Quote=引用 2 楼 SQL77 的回复:]
SQL code
SELECT A.*,CASE WHEN EXISTS(SELECT 1 FROM B WHERE A.ID=B.列1) THEN 'TRUE' ELSE 'FALSE' END AS 'C'
FROM A
[/Quote]正解。

[usher_gml]

select a.*,case when b.列2 is null then 'trun' else 'false' end as C
from a left join b on a.id=B.列2

[feixianxxx]

if object_id('a') is not null drop table a
create table a(ID int,列1 int, 列2 int)
insert a
select 1,1,1
union all select 2,2,2
union all select 3,3,3
union all select 4,4,4
union all select 5,5,5

if object_id('b') is not null drop table b
create table b (ID int,列1 int, 列2 int)
insert b
select 1,1,1
union all select 2,2,1
union all select 3,3,1
union all select 4,1,2
union all select 5,2,2




SELECT A.*,CASE WHEN EXISTS(SELECT 1 FROM B WHERE A.ID=B.列1) THEN 'TRUE' ELSE 'FALSE' END AS 'C'
FROM A
/*
1	1	1	TRUE
2	2	2	TRUE
3	3	3	TRUE
4	4	4	FALSE
5	5	5	FALSE  */

[仙道彰]

[Quote=引用 10 楼 feixianxxx 的回复:]
SQL code
if object_id('a') is not null drop table a
create table a(ID int,列1 int, 列2 int)
insert a
select 1,1,1
union all select 2,2,2
union all select 3,3,3
union all select 4,4,4
union all select 5,5,5

if object_id('b') is not null drop table b
create table b (ID int,列1 int, 列2 int)
insert b
select 1,1,1
union all select 2,2,1
union all select 3,3,1
union all select 4,1,2
…
[/Quote]

[「已注销」]

速度够快。。。。。。

[jinlingoo1]

create table A ([ID] INT, [列1] INT, [列2] INT)
INSERT A
SELECT 1, 1, 1 UNION ALL
SELECT 2, 2, 2 UNION ALL
SELECT 3, 3, 3 UNION ALL
SELECT 4, 4, 4 UNION ALL
SELECT 5, 5, 5

create table B ([ID] INT, [列1] INT, [列2] INT)
INSERT B
SELECT 1, 1, 1 UNION ALL
SELECT 2, 2, 1 UNION ALL
SELECT 3, 3, 1 UNION ALL
SELECT 4, 1, 2 UNION ALL
SELECT 5, 2, 2


如果表相符,直接运行以下SQL
select M.*,case when N.列1 is null then 'false' else 'ture' end
from
(select* from A) M left join
(select * from B where 列2=1) N
on M.ID=N.ID

[nalnait]

xuexi

[ai_li7758521]

if object_id('a') is not null drop table a
create table a(ID int,列1 int, 列2 int)
insert a
select 1,1,1 union all
select 2,2,2 union all
select 3,3,3 union all
select 4,4,4 union all
select 5,5,5

if object_id('b') is not null drop table b
create table b (ID int,列1 int, 列2 int)
insert b
select 1,1,1 union all
select 2,2,1 union all
select 3,3,1 union all
select 4,4,2 union all
select 5,5,2

SELECT A.*,C=(CASE WHEN EXISTS(SELECT 1 FROM B WHERE A.ID=B.列1 AND B.列2=1) THEN 'TRUE' ELSE 'FALSE' END)
FROM A

ID          列1          列2          C
----------- ----------- ----------- -----
1           1           1           TRUE
2           2           2           TRUE
3           3           3           TRUE
4           4           4           FALSE
5           5           5           FALSE

(5 行受影响)