DECLARE @ta TABLE([d_id] int, [department] varchar(10))
INSERT INTO @ta
SELECT 1, '信息系'
UNION ALL SELECT 2, '工管信'
UNION ALL SELECT 3, '中文系'
DECLARE @tb TABLE([id] int, [title] varchar(10), [d_id] int)
INSERT INTO @tb
SELECT 1, 'qqqq', 3
UNION ALL SELECT 2, 'wwww', 2
UNION ALL SELECT 3, 'qqqq', 1
UNION ALL SELECT 4, 'wwww', 2SELECT A.d_id, A.department, SUM(CASE WHEN B.title IS NULL THEN 0 ELSE 1 END) AS CT
FROM @ta A
LEFT JOIN @tb B ON A.d_id = B.d_id
GROUP BY A.d_id, A.department
ORDER BY A.d_id
d_id department CT
----------- ---------- -----------
1 信息系 1
2 工管信 2
3 中文系 1
INSERT INTO @ta
SELECT 1, '信息系'
UNION ALL SELECT 2, '工管信'
UNION ALL SELECT 3, '中文系'
DECLARE @tb TABLE([id] int, [title] varchar(10), [d_id] int)
INSERT INTO @tb
SELECT 1, 'qqqq', 3
UNION ALL SELECT 2, 'wwww', 2
UNION ALL SELECT 3, 'qqqq', 1
UNION ALL SELECT 4, 'wwww', 2SELECT A.d_id, A.department, SUM(CASE WHEN B.title IS NULL THEN 0 ELSE 1 END) AS CT
FROM @ta A
LEFT JOIN @tb B ON A.d_id = B.d_id
GROUP BY A.d_id, A.department
ORDER BY A.d_id
d_id department CT
----------- ---------- -----------
1 信息系 1
2 工管信 2
3 中文系 1
解决方案 »
- 求指点!这种情况下的sql语句怎么写?谢谢
- 插入一组测量数据时如何才能效率高?
- ▲▲▲数据恢复问题,在原有的hy数据库上选择还原数据库并将选项下的原有的还原为c:/data/hy.mdf改为...▲▲▲
- 请问*.data是什么文件
- 数据库变成7G,但真正存的数据只有1G多,那位高手帮忙下
- 求SQL2000/32升级SQL2005/64升级方法
- 大家给我看一下这个sql语句那里错了??急
- db2问题,n简单
- 使用inner join和where 语句来约束查询条件,那一个效率更高,请说明原因
- 哪位大虾能详细介绍下ADO、ODBC还有其它的一些数据源的区别阿?
- 如何用存储过程删除连接服务器?
- 身份证同号查询
insert into table1
select 1,'信息系'
union all select 2,'工管信'
union all select 3,'中文系'create table table2(id int,title varchar(10),d_id int)
insert into table2
select 1,'qqqq',3
union all select 2,'wwww',2
union all select 3,'qqqq',1
union all select 4,'wwww',2select table1.d_id,table1.department,t.total from table1
inner join (select d_id,count(*) total from table2 group by d_id)t on table1.d_id=t.d_id
INSERT INTO @ta
SELECT 1, '信息系'
UNION ALL SELECT 2, '工管信'
UNION ALL SELECT 3, '中文系'
DECLARE @tb TABLE([id] int, [title] varchar(10), [d_id] int)
INSERT INTO @tb
SELECT 1, 'qqqq', 3
UNION ALL SELECT 2, 'wwww', 2
UNION ALL SELECT 3, 'qqqq', 1
UNION ALL SELECT 4, 'wwww', 2select a.department,b.数量 from @ta a ,(
select d_id , count(*) as 数量 from @tb group by d_id) b
where a.d_id = b.d_iddepartment 数量
---------- -----------
信息系 1
工管信 2
中文系 1(所影响的行数为 3 行)
INSERT INTO @ta
SELECT 1, '信息系'
UNION ALL SELECT 2, '工管信'
UNION ALL SELECT 3, '中文系'
DECLARE @tb TABLE([id] int, [title] varchar(10), [d_id] int)
INSERT INTO @tb
SELECT 1, 'qqqq', 3
UNION ALL SELECT 2, 'wwww', 2
UNION ALL SELECT 3, 'qqqq', 1
UNION ALL SELECT 4, 'wwww', 2SELECT A.d_id, A.department, B.CT
FROM @ta A
LEFT JOIN (SELECT d_id, COUNT(1) AS CT FROM @tb GROUP BY d_id)B ON A.d_id = B.d_id
ORDER BY A.d_id
COMPUTE SUM(B.CT)
DECLARE @ta TABLE([d_id] int, [department] varchar(10))
INSERT INTO @ta
SELECT 1, '信息系'
UNION ALL SELECT 2, '工管信'
UNION ALL SELECT 3, '中文系'
DECLARE @tb TABLE([id] int, [title] varchar(10), [d_id] int)
INSERT INTO @tb
SELECT 1, 'qqqq', 3
UNION ALL SELECT 2, 'wwww', 2
UNION ALL SELECT 3, 'qqqq', 1
UNION ALL SELECT 4, 'wwww', 2select isnull(a.department ,'总计') as department,b.数量 from @ta a full join (
select d_id , count(*) as 数量 from @tb group by d_id with rollup) b
on cast(a.d_id as char(4)) = b.d_iddepartment 数量
---------- -----------
信息系 1
工管信 2
中文系 1
总计 4(所影响的行数为 4 行)