UPDATE A SET
频次 = B.cnt
FROM tb1 AS A
JOIN (
SELECT 药名称,COUNT(*) AS cnt
FROM tb2
WHERE 病名 = '病1'
GROUP BY 药名称
) AS B
ON A.药名称=B.药名称
频次 = B.cnt
FROM tb1 AS A
JOIN (
SELECT 药名称,COUNT(*) AS cnt
FROM tb2
WHERE 病名 = '病1'
GROUP BY 药名称
) AS B
ON A.药名称=B.药名称
-- Author : liangCK 小梁 & angellan 兰儿
-- Comment: 小梁 爱 兰儿
-- Date : 2009-05-31 14:58:46
-------------------------------------------
--> 生成测试数据: @tb1
DECLARE @tb1 TABLE (序号 INT,药名称 VARCHAR(3),频次 INT)
INSERT INTO @tb1
SELECT 1,'药2',null UNION ALL
SELECT 2,'药3',null UNION ALL
SELECT 3,'药4',null
--> 生成测试数据: @tb2
DECLARE @tb2 TABLE (方ID INT,药名称 VARCHAR(3),病名 VARCHAR(3))
INSERT INTO @tb2
SELECT 1,'药1','病1' UNION ALL
SELECT 1,'药2','病1' UNION ALL
SELECT 2,'药2','病1' UNION ALL
SELECT 2,'药4','病1' UNION ALL
SELECT 2,'药1','病1' UNION ALL
SELECT 3,'药1','病1' UNION ALL
SELECT 3,'药3','病1' UNION ALL
SELECT 3,'药2','病1' UNION ALL
SELECT 4,'药1','病2' UNION ALL
SELECT 4,'药3','病2' UNION ALL
SELECT 4,'药2','病2'--SQL查询如下:UPDATE A SET
频次 = B.cnt
FROM @tb1 AS A
JOIN (
SELECT 药名称,COUNT(*) AS cnt
FROM @tb2
WHERE 病名 = '病1'
GROUP BY 药名称
) AS B
ON A.药名称=B.药名称;SELECT * FROM @tb1;/*
(3 row(s) affected)
序号 药名称 频次
----------- ---- -----------
1 药2 3
2 药3 1
3 药4 1(3 row(s) affected)*/
go
create table [表1]([序号] int,[药名称] varchar(3),[频次] int)
insert [表1]
select 1,'药2',null union all
select 2,'药3',null union all
select 3,'药4',null
go
if object_id('[表2]') is not null drop table [表2]
go
create table [表2]([方ID] int,[药名称] varchar(3),[病名] varchar(3))
insert [表2]
select 1,'药1','病1' union all
select 1,'药2','病1' union all
select 2,'药2','病1' union all
select 2,'药4','病1' union all
select 2,'药1','病1' union all
select 3,'药1','病1' union all
select 3,'药3','病1' union all
select 3,'药2','病1' union all
select 4,'药1','病2' union all
select 4,'药3','病2' union all
select 4,'药2','病2'
go
--select * from [表1]
--select * from [表2]select a.序号,a.药名称,频次=count(b.病名)
from 表1 a left join 表2 b
on a.药名称=b.药名称 and b.病名='病1'
group by a.序号,a.药名称
/*
序号 药名称 频次
----------- ---- -----------
1 药2 3
2 药3 1
3 药4 1(3 行受影响)
*/
create TABLE #UU(序号 INT,药名称 VARCHAR(3),频次 INT)
INSERT INTO #UU
SELECT 1,'药2',null UNION ALL
SELECT 2,'药3',null UNION ALL
SELECT 3,'药4',null
create TABLE #HH (方ID INT,药名称 VARCHAR(3),病名 VARCHAR(3))
INSERT INTO #HH
SELECT 1,'药1','病1' UNION ALL
SELECT 1,'药2','病1' UNION ALL
SELECT 2,'药2','病1' UNION ALL
SELECT 2,'药4','病1' UNION ALL
SELECT 2,'药1','病1' UNION ALL
SELECT 3,'药1','病1' UNION ALL
SELECT 3,'药3','病1' UNION ALL
SELECT 3,'药2','病1' UNION ALL
SELECT 4,'药1','病2' UNION ALL
SELECT 4,'药3','病2' UNION ALL
SELECT 4,'药2','病2'
select U.序号,U.药名称,count(H.药名称) 频次 from #HH H join #UU U
on H.药名称=U.药名称 and 病名='病1' group by U.序号,U.药名称序号 药名称 频次
----------- ---- -----------
1 药2 3
2 药3 1
3 药4 1(3 行受影响)
频次 = B.cnt
FROM @tb1 AS A
JOIN (
SELECT 药名称,COUNT(*) AS cnt
FROM @tb2
WHERE 病名 = '病1'
GROUP BY 药名称
) AS B
ON A.药名称=B.药名称
WHERE A.药名称='xxx';
-- Author : liangCK 小梁 & angellan 兰儿
-- Comment: 小梁 爱 兰儿
-- Date : 2009-05-31 14:58:46
-------------------------------------------
--> 生成测试数据: @tb1
DECLARE @tb1 TABLE (序号 INT,药名称 VARCHAR(3),频次 INT)
INSERT INTO @tb1
SELECT 1,'药2',null UNION ALL
SELECT 2,'药3',null UNION ALL
SELECT 3,'药4',null
--> 生成测试数据: @tb2
DECLARE @tb2 TABLE (方ID INT,药名称 VARCHAR(3),病名 VARCHAR(3))
INSERT INTO @tb2
SELECT 1,'药1','病1' UNION ALL
SELECT 1,'药2','病1' UNION ALL
SELECT 2,'药2','病1' UNION ALL
SELECT 2,'药4','病1' UNION ALL
SELECT 2,'药1','病1' UNION ALL
SELECT 3,'药1','病1' UNION ALL
SELECT 3,'药3','病1' UNION ALL
SELECT 3,'药2','病1' UNION ALL
SELECT 4,'药1','病2' UNION ALL
SELECT 4,'药3','病2' UNION ALL
SELECT 4,'药2','病2'--SQL查询如下:UPDATE A SET
频次 = B.cnt
FROM @tb1 AS A
JOIN (
SELECT 药名称,COUNT(*) AS cnt
FROM @tb2
WHERE 病名 = '病1'
GROUP BY 药名称
) AS B
ON A.药名称=B.药名称;SELECT * FROM @tb1;/*
(3 row(s) affected)
序号 药名称 频次
----------- ---- -----------
1 药2 3
2 药3 1
3 药4 1(3 row(s) affected)*/