left join地疑难！！！着急...

A表
id  typeName
1  数据更新
2  天气预报
3  主机维修
4  病毒感染

B表

id typeName  addTime    userName
1  病毒感染  2008-1-1      小张
2  主机维修  2008-1-3      小李
3  病毒感染  2008-1-2      小张
4  数据更新  2008-1-1      小张
5  主机硬件  2008-2-1      小张

我想要一个效果就是如下：

      姓名    类型      次数
    小张  数据更新    1
    小张  天气预报    0
    小张  主机维修    1
    小张  病毒感染    2
    小李  数据更新    0
    小李  天气预报    0
    小李  主机维修    1
    小李  病毒感染    0

怎么写sql语句？

---

这个问题第1个回答：

SQL code

select userName,typeName,次數 = count(1) from A,B group by userName ,typeName

---

这个问题第2个回答：

SQL code

select  x.userName as 姓名,a.typeName as 类型,

count(b.userName) as 次数 

from a cross join (select distinct userName from b) x

left join b

on a.typeName=b.typeName and x.userName=b.userName

group by x.userName ,a.typeName

---

这个问题第3个回答：

SQL code

--> --> 

 

declare @A table([id] int,[typeName] nvarchar(4))

Insert @A

select 1,N'数据更新' union all

select 2,N'天气预报' union all

select 3,N'主机维修' union all

select 4,N'病毒感染'

 

--> --> 

 

declare @B table([id] int,[typeName] nvarchar(4),[addTime] Datetime,[userName] nvarchar(2))

Insert @B

select 1,N'病毒感染','2008-1-1',N'小张' union all

select 2,N'主机维修','2008-1-3',N'小李' union all

select 3,N'病毒感染','2008-1-2',N'小张' union all

select 4,N'数据更新','2008-1-1',N'小张' union all

select 5,N'主机硬件','2008-2-1',N'小张'

 

select

    t1.[typeName],t1.[userName],count(t2.[id])記錄

from 

    (select * from (Select [userName] from @B group by [userName]) a,@a b)t1

left join

    @B t2 on t1.[typeName]=t2.[typeName] and t1.[userName]=t2.[userName]

group by t1.[userName] ,t1.[typeName]

order by t1.[userName] desc





(4 個資料列受到影響)



(5 個資料列受到影響)

typeName userName 記錄

-------- -------- -----------

天气预报     小张       0

主机维修     小张       0

病毒感染     小张       2

数据更新     小张       1

天气预报     小李       0

主机维修     小李       1

病毒感染     小李       0

数据更新     小李       0



(8 個資料列受到影響)

---

这个问题第4个回答：
..

---

这个问题第5个回答：
楼上已答

---

这个问题第6个回答：
select * into a from
(
select 1 as id, '数据更新' as typeName
union all
select 2 as id, '天气预报' as typeName
union all
select 3 as id, '主机维修' as typeName
union all
select 4 as id, '病毒感染' as typeName
) a

select * into b from
(
select 1 as id, '病毒感染' as typeName, '2008-1-1' as addTime, '小张' as userName
union all
select 2 as id, '主机维修' as typeName, '2008-1-3' as addTime, '小李' as userName
union all
select 3 as id, '病毒感染' as typeName, '2008-1-2' as addTime, '小张' as userName
union all
select 4 as id, '数据更新' as typeName, '2008-1-1' as addTime, '小张' as userName
union all
select 5 as id, '主机维修' as typeName, '2008-2-1' as addTime, '小张' as userName
) b

select u.userName, a.typeName, (select count(id) from b where b.userName = u.userName and b.typeName = a.typeName) as 次数
from
(
select userName
from b
group by userName
) u, a

drop table a, b

---

这个问题第7个回答：
group by

---

这个问题第8个回答：

SQL code

select

    t1.[typeName],t1.[userName],count(t2.[id])記錄

from 

    (select * from (Select [userName] from @B group by [userName]) a,@a b)t1

left join

    @B t2 on t1.[typeName]=t2.[typeName] and t1.[userName]=t2.[userName]

group by t1.[userName] ,t1.[typeName]

order by t1.[userName] desc

小写的a,b有什么用呢？

---

这个问题第9个回答：

SQL code

select

    t1.[typeName],t1.[userName],count(t2.[id])記錄

from 

    (select * from (Select [userName] from @B group by [userName]) a,@a b)t1

left join

    @B t2 on t1.[typeName]=t2.[typeName] and t1.[userName]=t2.[userName]

group by t1.[userName] ,t1.[typeName]

order by t1.[userName] desc

里面的小写的a,b有什么用呢？没看懂

---

这个问题第10个回答：
把兩個結果集作笛卡爾積

a,b為結果集別名

[1] [2] [3] [4] 下一页