select username, counta=SUM(case when cola=username then 1 else 0 end ), countb=SUM(case when colb=username then 1 else 0 end ), countc=SUM(case when colc=username then 1 else 0 end ), countc=SUM(case when colc=username then 1 else 0 end ), counte=SUM(case when cole=username then 1 else 0 end ) from a,B group by username
SELECT A.username, SUM(CASE WHEN B.flag = 'colA' THEN 1 ELSE 0 END) AS count_A, SUM(CASE WHEN B.flag = 'colB' THEN 1 ELSE 0 END) AS count_B, SUM(CASE WHEN B.flag = 'colC' THEN 1 ELSE 0 END) AS count_C, SUM(CASE WHEN B.flag = 'colD' THEN 1 ELSE 0 END) AS count_D, SUM(CASE WHEN B.flag = 'colE' THEN 1 ELSE 0 END) AS count_E FROM tb1 AS A LEFT JOIN ( SELECT colA AS name,'colA' AS flag FROM tb2 WHERE colA IS NOT NULL UNION ALL SELECT colB,'colB' FROM tb2 WHERE colB IS NOT NULL UNION ALL SELECT colC,'colC' FROM tb2 WHERE colC IS NOT NULL UNION ALL SELECT colD,'colD' FROM tb2 WHERE colD IS NOT NULL UNIOIN ALL SELECT colE,'colE' FROM tb2 WHERE colE IS NOT NULL ) AS B ON A.username = B.name GROUP BY A.username;
--> 生成测试数据: @tb1 DECLARE @tb1 TABLE (username VARCHAR(6)) INSERT INTO @tb1 SELECT '刘丽娜' UNION ALL SELECT '刘更胜' UNION ALL SELECT '王英' UNION ALL SELECT '吴洪波' UNION ALL SELECT '王玉江' UNION ALL SELECT '薛静' UNION ALL SELECT '王前进' UNION ALL SELECT '武文明' UNION ALL SELECT '胡家和'
--> 生成测试数据: @tb2 DECLARE @tb2 TABLE (colA VARCHAR(6),colB VARCHAR(6),colC VARCHAR(6),coLD VARCHAR(6),colE VARCHAR(6)) INSERT INTO @tb2 SELECT '苏凤林','李拥军',null,'刘丽娜','靳文礼' UNION ALL SELECT '王英','李拥军','宋建刚',null,'刘更胜' UNION ALL SELECT '任小东','李拥军',null,null,'刘丽娜' UNION ALL SELECT '姚玉刚',null,'姚玉刚','宋建刚','姚玉刚' UNION ALL SELECT '宋建刚',null,null,'靳文礼','任小东' UNION ALL SELECT '姚玉刚',null,'任小东',null,'刘丽娜' UNION ALL SELECT null,null,'任小东','姚玉刚','刘丽娜' UNION ALL SELECT null,null,null,null,'刘丽娜' UNION ALL SELECT null,null,'宋建刚',null,'刘丽娜' UNION ALL SELECT '靳文礼',null,null,'刘丽娜','刘丽娜' UNION ALL SELECT '任小东',null,null,'宋建刚','靳文礼' UNION ALL SELECT '刘丽娜',null,'姚玉刚',null,'宋建刚' UNION ALL SELECT null,null,null,'姚玉刚','宋建刚' UNION ALL SELECT null,null,null,null,'宋建刚' UNION ALL SELECT null,null,'任小东',null,'靳文礼' UNION ALL SELECT '刘丽娜',null,null,'宋建刚','靳文礼' UNION ALL SELECT null,null,'任小东',null,null--SQL查询如下:SELECT A.username, SUM(CASE WHEN B.flag = 'colA' THEN 1 ELSE 0 END) AS count_A, SUM(CASE WHEN B.flag = 'colB' THEN 1 ELSE 0 END) AS count_B, SUM(CASE WHEN B.flag = 'colC' THEN 1 ELSE 0 END) AS count_C, SUM(CASE WHEN B.flag = 'colD' THEN 1 ELSE 0 END) AS count_D, SUM(CASE WHEN B.flag = 'colE' THEN 1 ELSE 0 END) AS count_E FROM @tb1 AS A LEFT JOIN ( SELECT colA AS name,'colA' AS flag FROM @tb2 WHERE colA IS NOT NULL UNION ALL SELECT colB,'colB' FROM @tb2 WHERE colB IS NOT NULL UNION ALL SELECT colC,'colC' FROM @tb2 WHERE colC IS NOT NULL UNION ALL SELECT colD,'colD' FROM @tb2 WHERE colD IS NOT NULL UNION ALL SELECT colE,'colE' FROM @tb2 WHERE colE IS NOT NULL ) AS B ON A.username = B.name GROUP BY A.username;/* username count_A count_B count_C count_D count_E -------- ----------- ----------- ----------- ----------- ----------- 胡家和 0 0 0 0 0 刘更胜 0 0 0 0 1 刘丽娜 2 0 0 2 6 王前进 0 0 0 0 0 王英 1 0 0 0 0 王玉江 0 0 0 0 0 吴洪波 0 0 0 0 0 武文明 0 0 0 0 0 薛静 0 0 0 0 0(9 行受影响) */
--> 测试时间:2009-07-08 17:23:15 --> 我的淘宝: http://shop36766744.taobao.com/if object_id('[表A]') is not null drop table [表A] create table [表A]([username] varchar(6)) insert [表A] select '刘丽娜' union all select '刘更胜' union all select '王英' union all select '吴洪波' union all select '王玉江' union all select '薛静' union all select '王前进' union all select '武文明' union all select '胡家和' if object_id('[表B]') is not null drop table [表B] create table [表B]([colA] varchar(6),[colB] varchar(6),[colC] varchar(6),[coLD] varchar(6),[colE] varchar(6)) insert [表B] select '苏凤林','李拥军',null,'刘丽娜','靳文礼' union all select '王英','李拥军','宋建刚',null,'刘更胜' union all select '任小东','李拥军',null,null,'刘丽娜' union all select '姚玉刚',null,'姚玉刚','宋建刚','姚玉刚' union all select '宋建刚',null,null,'靳文礼','任小东' union all select '姚玉刚',null,'任小东',null,'刘丽娜' union all select null,null,'任小东','姚玉刚','刘丽娜' union all select null,null,null,null,'刘丽娜' union all select null,null,'宋建刚',null,'刘丽娜' union all select '靳文礼',null,null,'刘丽娜','刘丽娜' union all select '任小东',null,null,'宋建刚','靳文礼' union all select '刘丽娜',null,'姚玉刚',null,'宋建刚' union all select null,null,null,'姚玉刚','宋建刚' union all select null,null,null,null,'宋建刚' union all select null,null,'任小东',null,'靳文礼' union all select '刘丽娜',null,null,'宋建刚','靳文礼' union all select null,null,'任小东',null,nullselect A.username, countA=sum(case when flat='A' and A.username=B.username then 1 else 0 end ), countB=sum(case when flat='B' and A.username=B.username then 1 else 0 end ), countC=sum(case when flat='C' and A.username=B.username then 1 else 0 end ), countD=sum(case when flat='D' and A.username=B.username then 1 else 0 end ), countE=sum(case when flat='E' and A.username=B.username then 1 else 0 end ) from( select flat='A',username=colA from [表B] union all select flat='B',username=colB from [表B] union all select flat='C',username=colC from [表B] union all select flat='D',username=colD from [表B] union all select flat='E',username=colE from [表B])B,表A A group by A.username /* username countA countB countC countD countE -------- ----------- ----------- ----------- ----------- ----------- 王英 1 0 0 0 0 刘更胜 0 0 0 0 1 刘丽娜 2 0 0 2 6 薛静 0 0 0 0 0 吴洪波 0 0 0 0 0 胡家和 0 0 0 0 0 王玉江 0 0 0 0 0 王前进 0 0 0 0 0 武文明 0 0 0 0 0(所影响的行数为 9 行)*/drop table 表A,表B
sum case when 这些应该是大部分数据库都支持的吧 自己想了大半天没想出来 上网10分钟就搞定了 应该检讨一下解决问题的方法
counta=SUM(case when cola=username then 1 else 0 end ),
countb=SUM(case when colb=username then 1 else 0 end ),
countc=SUM(case when colc=username then 1 else 0 end ),
countc=SUM(case when colc=username then 1 else 0 end ),
counte=SUM(case when cole=username then 1 else 0 end )
from a,B
group by username
A.username,
SUM(CASE WHEN B.flag = 'colA' THEN 1 ELSE 0 END) AS count_A,
SUM(CASE WHEN B.flag = 'colB' THEN 1 ELSE 0 END) AS count_B,
SUM(CASE WHEN B.flag = 'colC' THEN 1 ELSE 0 END) AS count_C,
SUM(CASE WHEN B.flag = 'colD' THEN 1 ELSE 0 END) AS count_D,
SUM(CASE WHEN B.flag = 'colE' THEN 1 ELSE 0 END) AS count_E
FROM tb1 AS A
LEFT JOIN (
SELECT colA AS name,'colA' AS flag FROM tb2 WHERE colA IS NOT NULL
UNION ALL
SELECT colB,'colB' FROM tb2 WHERE colB IS NOT NULL
UNION ALL
SELECT colC,'colC' FROM tb2 WHERE colC IS NOT NULL
UNION ALL
SELECT colD,'colD' FROM tb2 WHERE colD IS NOT NULL
UNIOIN ALL
SELECT colE,'colE' FROM tb2 WHERE colE IS NOT NULL
) AS B
ON A.username = B.name
GROUP BY A.username;
-- Author : liangCK 梁爱兰
-- Comment: 小梁 爱 兰儿
-- Date : 2009-07-08 17:27:06
-------------------------------------
--> 生成测试数据: @tb1
DECLARE @tb1 TABLE (username VARCHAR(6))
INSERT INTO @tb1
SELECT '刘丽娜' UNION ALL
SELECT '刘更胜' UNION ALL
SELECT '王英' UNION ALL
SELECT '吴洪波' UNION ALL
SELECT '王玉江' UNION ALL
SELECT '薛静' UNION ALL
SELECT '王前进' UNION ALL
SELECT '武文明' UNION ALL
SELECT '胡家和'
--> 生成测试数据: @tb2
DECLARE @tb2 TABLE (colA VARCHAR(6),colB VARCHAR(6),colC VARCHAR(6),coLD VARCHAR(6),colE VARCHAR(6))
INSERT INTO @tb2
SELECT '苏凤林','李拥军',null,'刘丽娜','靳文礼' UNION ALL
SELECT '王英','李拥军','宋建刚',null,'刘更胜' UNION ALL
SELECT '任小东','李拥军',null,null,'刘丽娜' UNION ALL
SELECT '姚玉刚',null,'姚玉刚','宋建刚','姚玉刚' UNION ALL
SELECT '宋建刚',null,null,'靳文礼','任小东' UNION ALL
SELECT '姚玉刚',null,'任小东',null,'刘丽娜' UNION ALL
SELECT null,null,'任小东','姚玉刚','刘丽娜' UNION ALL
SELECT null,null,null,null,'刘丽娜' UNION ALL
SELECT null,null,'宋建刚',null,'刘丽娜' UNION ALL
SELECT '靳文礼',null,null,'刘丽娜','刘丽娜' UNION ALL
SELECT '任小东',null,null,'宋建刚','靳文礼' UNION ALL
SELECT '刘丽娜',null,'姚玉刚',null,'宋建刚' UNION ALL
SELECT null,null,null,'姚玉刚','宋建刚' UNION ALL
SELECT null,null,null,null,'宋建刚' UNION ALL
SELECT null,null,'任小东',null,'靳文礼' UNION ALL
SELECT '刘丽娜',null,null,'宋建刚','靳文礼' UNION ALL
SELECT null,null,'任小东',null,null--SQL查询如下:SELECT
A.username,
SUM(CASE WHEN B.flag = 'colA' THEN 1 ELSE 0 END) AS count_A,
SUM(CASE WHEN B.flag = 'colB' THEN 1 ELSE 0 END) AS count_B,
SUM(CASE WHEN B.flag = 'colC' THEN 1 ELSE 0 END) AS count_C,
SUM(CASE WHEN B.flag = 'colD' THEN 1 ELSE 0 END) AS count_D,
SUM(CASE WHEN B.flag = 'colE' THEN 1 ELSE 0 END) AS count_E
FROM @tb1 AS A
LEFT JOIN (
SELECT colA AS name,'colA' AS flag FROM @tb2 WHERE colA IS NOT NULL
UNION ALL
SELECT colB,'colB' FROM @tb2 WHERE colB IS NOT NULL
UNION ALL
SELECT colC,'colC' FROM @tb2 WHERE colC IS NOT NULL
UNION ALL
SELECT colD,'colD' FROM @tb2 WHERE colD IS NOT NULL
UNION ALL
SELECT colE,'colE' FROM @tb2 WHERE colE IS NOT NULL
) AS B
ON A.username = B.name
GROUP BY A.username;/*
username count_A count_B count_C count_D count_E
-------- ----------- ----------- ----------- ----------- -----------
胡家和 0 0 0 0 0
刘更胜 0 0 0 0 1
刘丽娜 2 0 0 2 6
王前进 0 0 0 0 0
王英 1 0 0 0 0
王玉江 0 0 0 0 0
吴洪波 0 0 0 0 0
武文明 0 0 0 0 0
薛静 0 0 0 0 0(9 行受影响)
*/
--> 测试时间:2009-07-08 17:23:15
--> 我的淘宝: http://shop36766744.taobao.com/if object_id('[表A]') is not null drop table [表A]
create table [表A]([username] varchar(6))
insert [表A]
select '刘丽娜' union all
select '刘更胜' union all
select '王英' union all
select '吴洪波' union all
select '王玉江' union all
select '薛静' union all
select '王前进' union all
select '武文明' union all
select '胡家和'
if object_id('[表B]') is not null drop table [表B]
create table [表B]([colA] varchar(6),[colB] varchar(6),[colC] varchar(6),[coLD] varchar(6),[colE] varchar(6))
insert [表B]
select '苏凤林','李拥军',null,'刘丽娜','靳文礼' union all
select '王英','李拥军','宋建刚',null,'刘更胜' union all
select '任小东','李拥军',null,null,'刘丽娜' union all
select '姚玉刚',null,'姚玉刚','宋建刚','姚玉刚' union all
select '宋建刚',null,null,'靳文礼','任小东' union all
select '姚玉刚',null,'任小东',null,'刘丽娜' union all
select null,null,'任小东','姚玉刚','刘丽娜' union all
select null,null,null,null,'刘丽娜' union all
select null,null,'宋建刚',null,'刘丽娜' union all
select '靳文礼',null,null,'刘丽娜','刘丽娜' union all
select '任小东',null,null,'宋建刚','靳文礼' union all
select '刘丽娜',null,'姚玉刚',null,'宋建刚' union all
select null,null,null,'姚玉刚','宋建刚' union all
select null,null,null,null,'宋建刚' union all
select null,null,'任小东',null,'靳文礼' union all
select '刘丽娜',null,null,'宋建刚','靳文礼' union all
select null,null,'任小东',null,nullselect A.username,
countA=sum(case when flat='A' and A.username=B.username then 1 else 0 end ),
countB=sum(case when flat='B' and A.username=B.username then 1 else 0 end ),
countC=sum(case when flat='C' and A.username=B.username then 1 else 0 end ),
countD=sum(case when flat='D' and A.username=B.username then 1 else 0 end ),
countE=sum(case when flat='E' and A.username=B.username then 1 else 0 end )
from(
select flat='A',username=colA from [表B] union all
select flat='B',username=colB from [表B] union all
select flat='C',username=colC from [表B] union all
select flat='D',username=colD from [表B] union all
select flat='E',username=colE from [表B])B,表A A
group by A.username
/*
username countA countB countC countD countE
-------- ----------- ----------- ----------- ----------- -----------
王英 1 0 0 0 0
刘更胜 0 0 0 0 1
刘丽娜 2 0 0 2 6
薛静 0 0 0 0 0
吴洪波 0 0 0 0 0
胡家和 0 0 0 0 0
王玉江 0 0 0 0 0
王前进 0 0 0 0 0
武文明 0 0 0 0 0(所影响的行数为 9 行)*/drop table 表A,表B
自己想了大半天没想出来 上网10分钟就搞定了
应该检讨一下解决问题的方法