表:a
ID createdate type
-----------------------------
1 2010-03-01 1
2 2010-03-02 1
3 2010-03-02 2
4 2010-03-04 2
5 2010-03-05 2
6 2010-03-05 4
7 2010-03-05 3
8 2010-03-07 2
我想要查出这样的结果:
createdate type1 type2
---------------------------------
2010-03-01 yes no
2010-03-02 yes yes
2010-03-04 no yes
2010-03-05 no yes
2010-03-07 no yes
就是type为1和2的如果当天有为yes,没有为no
ID createdate type
-----------------------------
1 2010-03-01 1
2 2010-03-02 1
3 2010-03-02 2
4 2010-03-04 2
5 2010-03-05 2
6 2010-03-05 4
7 2010-03-05 3
8 2010-03-07 2
我想要查出这样的结果:
createdate type1 type2
---------------------------------
2010-03-01 yes no
2010-03-02 yes yes
2010-03-04 no yes
2010-03-05 no yes
2010-03-07 no yes
就是type为1和2的如果当天有为yes,没有为no
INSERT @a select 1 ,'2010-03-01', 1
union all SELECT 2 ,'2010-03-02', 1
union all SELECT 3 ,'2010-03-02', 2
union all SELECT 4 ,'2010-03-04', 2
union all SELECT 5 ,'2010-03-05', 2
union all SELECT 6 ,'2010-03-05', 4
union all SELECT 7 ,'2010-03-05', 3
union all SELECT 8 ,'2010-03-07', 2
SELECT createdate,
TYPE1=(CASE WHEN exists(select 1 from @a where createdate=a.createdate AND TYPE=1) THEN 'yes' ELSE 'no' END),
TYPE2=(CASE WHEN exists(select 1 from @a where createdate=a.createdate AND TYPE=2) THEN 'yes' ELSE 'no' END)
FROM @a a
GROUP BY a.createdate--result
/*createdate TYPE1 TYPE2
-------------------- ----- -----
2010-03-01 yes no
2010-03-02 yes yes
2010-03-04 no yes
2010-03-05 no yes
2010-03-07 no yes(所影响的行数为 5 行)*/
--> 测试数据:[a1]
if object_id('[a]') is not null drop table [a]
create table [a]([ID] int,[createdate] datetime,[type] int)
insert [a]
select 1,'2010-03-01',1 union all
select 2,'2010-03-02',1 union all
select 3,'2010-03-02',2 union all
select 4,'2010-03-04',2 union all
select 5,'2010-03-05',2 union all
select 6,'2010-03-05',4 union all
select 7,'2010-03-05',3 union all
select 8,'2010-03-07',2select * from [a]SELECT createdate,[TYPE1]= CASE WHEN exists(SELECT 1 FROM a WHERE T.createdate = createdate AND TYPE = 1) THEN 'Yes' ELSE 'No' END,
[TYPE2]= CASE WHEN exists(SELECT 1 FROM a WHERE T.createdate = createdate AND TYPE = 2) THEN 'Yes' ELSE 'No' end
FROM dbo.a T
GROUP BY createdate/*
createdate TYPE1 TYPE2
2010-03-01 00:00:00.000 Yes No
2010-03-02 00:00:00.000 Yes Yes
2010-03-04 00:00:00.000 No Yes
2010-03-05 00:00:00.000 No Yes
2010-03-07 00:00:00.000 No Yes*/
DECLARE @a table(ID INT, createdate varchar(20), TYPE INT)
INSERT @a select 1 ,'2010-03-01', 1
union all SELECT 2 ,'2010-03-02', 1
union all SELECT 3 ,'2010-03-02', 2
union all SELECT 4 ,'2010-03-04', 2
union all SELECT 5 ,'2010-03-05', 2
union all SELECT 6 ,'2010-03-05', 4
union all SELECT 7 ,'2010-03-05', 3
union all SELECT 8 ,'2010-03-07', 2SELECT createdate,
TYPE1=case when sum(CASE WHEN TYPE=1 THEN 1 ELSE 0 END)>0 THEN 'yes' ELSE 'no' END,
TYPE2=case when sum(CASE WHEN TYPE=2 THEN 1 ELSE 0 END)>0 THEN 'yes' ELSE 'no' END
FROM @a a
GROUP BY a.createdate--result
/*createdate TYPE1 TYPE2
-------------------- ----- -----
2010-03-01 yes no
2010-03-02 yes yes
2010-03-04 no yes
2010-03-05 no yes
2010-03-07 no yes(所影响的行数为 5 行)*/
createdate,
(case when exists(select 1 from tb where createdate=t.createdate and [type]=1) then 'yes' else 'no' end) as TYPE1,
(case when exists(select 1 from tb where createdate=t.createdate AND [type]=2) then 'yes' else 'no' end) as TYPE2
from
tb t
group by
createdate
insert [a]
select 1,'2010-03-01',1 union all
select 2,'2010-03-02',1 union all
select 3,'2010-03-02',2 union all
select 4,'2010-03-04',2 union all
select 5,'2010-03-05',2 union all
select 6,'2010-03-05',4 union all
select 7,'2010-03-05',3 union all
select 8,'2010-03-07',2select createdate,
max(case type when 1 then 'yes' else 'no' end) type1,
max(case type when 2 then 'yes' else 'no' end) type2
from a where type in (1,2)
group by createdatedrop table a/*
createdate type1 type2
------------------------------------------------------ ----- -----
2010-03-01 00:00:00.000 yes no
2010-03-02 00:00:00.000 yes yes
2010-03-04 00:00:00.000 no yes
2010-03-05 00:00:00.000 no yes
2010-03-07 00:00:00.000 no yes(所影响的行数为 5 行)
*.