现有两表:T2表、T1表,其中两表都为n行n列不只列出的这几列,传入参数 @p (给定为0.5)如何实现这样的功能:
1)循环T1表的每一行,取每一行value的值,判断两种情况,flag为1,flag为-1。
2)当T1表中flag值为1时,取value的值,并在T2中找列分别为这两个名称的列,当且仅当两个值同时为1,所有这样记录的数用num表示,再统计这两个名称中在前面的名
称的列,记录该列所有值为1的记录,记为num1,再统计这两个名称中在后面的名称的列,记录该列所有值为1的记录,记为num2,计算比值num/num1,num/num2。
3)当T1表中flag值为-1时,与2)相同,只是取两个名称一个为1一个为0的,只要不同时为0或1即可,记为num,其余num1、num取法于2)相同取值为1的。以T1表的第一行value值为(西红柿,辣椒)为例说明:则须在T2表中找到两列在同一行为0,1的记录数num(0,1在前在后都行),在此num为3. 再判断T2表中西红柿值为1的行数用num1表示,此时的num1=5,同样在取出(西红柿,辣椒)中辣椒值为1的行数用num2表示,num2=6,得出比值与传入参数@p 比较。
如果num/num1>=@p 则更新表T1的status1值为'高级',如num/num1 <@p则更新表T1的status1值为'低级',
如果num/num2>=@p 则更新表T1的status2值为'高级',如num/num2 <@p则更新表T1的status2值为'低级'。
如T1表中的flag值为1时,则须要找两列都同时为1的行记录num。
目的就是这样。但要求在T1表中逐行对value判断,且flag不为0.请问如何写语句? SQL codeIF OBJECT_ID('dbo.T2') IS NOT NULL DROP TABLE T2
CREATE TABLE T2 (西红柿 INT,辣椒 INT,葡萄 INT,苹果 INT,茄子 INT)
INSERT INTO T2
SELECT 1,0,0,0,0 UNION ALL
SELECT 1,1,0,0,0 UNION ALL
SELECT 0,1,1,1,0 UNION ALL
SELECT 1,1,0,0,1 UNION ALL
SELECT 0,1,1,0,0 UNION ALL
SELECT 1,1,0,0,1 UNION ALL
SELECT 1,1,1,1,1
Select * from Tongji
IF OBJECT_ID('dbo.T1') IS NOT NULL DROP TABLE T1
CREATE TABLE T1 (value varchar(20),flag int,status1 varchar(10),status2 varchar(10))
INSERT INTO T1
SELECT '(西红柿,辣椒)',-1,null,null UNION ALL
SELECT '(西红柿,葡萄)',0,null,null UNION ALL
SELECT '(辣椒,葡萄)',-1,null,null UNION ALL
SELECT '(西红柿,苹果)',-1,null,null UNION ALL
SELECT '(辣椒,苹果)',1,null,null UNION ALL
SELECT '(葡萄,苹果)',-1,null,null UNION ALL
SELECT '(西红柿,茄子)',1,null,null UNION ALL
SELECT '(辣椒,茄子)',-1,null,null UNION ALL
SELECT '(葡萄,茄子)',0,null,null UNION ALL
SELECT '(苹果,茄子)',1,null,null
Select * from T1
1)循环T1表的每一行,取每一行value的值,判断两种情况,flag为1,flag为-1。
2)当T1表中flag值为1时,取value的值,并在T2中找列分别为这两个名称的列,当且仅当两个值同时为1,所有这样记录的数用num表示,再统计这两个名称中在前面的名
称的列,记录该列所有值为1的记录,记为num1,再统计这两个名称中在后面的名称的列,记录该列所有值为1的记录,记为num2,计算比值num/num1,num/num2。
3)当T1表中flag值为-1时,与2)相同,只是取两个名称一个为1一个为0的,只要不同时为0或1即可,记为num,其余num1、num取法于2)相同取值为1的。以T1表的第一行value值为(西红柿,辣椒)为例说明:则须在T2表中找到两列在同一行为0,1的记录数num(0,1在前在后都行),在此num为3. 再判断T2表中西红柿值为1的行数用num1表示,此时的num1=5,同样在取出(西红柿,辣椒)中辣椒值为1的行数用num2表示,num2=6,得出比值与传入参数@p 比较。
如果num/num1>=@p 则更新表T1的status1值为'高级',如num/num1 <@p则更新表T1的status1值为'低级',
如果num/num2>=@p 则更新表T1的status2值为'高级',如num/num2 <@p则更新表T1的status2值为'低级'。
如T1表中的flag值为1时,则须要找两列都同时为1的行记录num。
目的就是这样。但要求在T1表中逐行对value判断,且flag不为0.请问如何写语句? SQL codeIF OBJECT_ID('dbo.T2') IS NOT NULL DROP TABLE T2
CREATE TABLE T2 (西红柿 INT,辣椒 INT,葡萄 INT,苹果 INT,茄子 INT)
INSERT INTO T2
SELECT 1,0,0,0,0 UNION ALL
SELECT 1,1,0,0,0 UNION ALL
SELECT 0,1,1,1,0 UNION ALL
SELECT 1,1,0,0,1 UNION ALL
SELECT 0,1,1,0,0 UNION ALL
SELECT 1,1,0,0,1 UNION ALL
SELECT 1,1,1,1,1
Select * from Tongji
IF OBJECT_ID('dbo.T1') IS NOT NULL DROP TABLE T1
CREATE TABLE T1 (value varchar(20),flag int,status1 varchar(10),status2 varchar(10))
INSERT INTO T1
SELECT '(西红柿,辣椒)',-1,null,null UNION ALL
SELECT '(西红柿,葡萄)',0,null,null UNION ALL
SELECT '(辣椒,葡萄)',-1,null,null UNION ALL
SELECT '(西红柿,苹果)',-1,null,null UNION ALL
SELECT '(辣椒,苹果)',1,null,null UNION ALL
SELECT '(葡萄,苹果)',-1,null,null UNION ALL
SELECT '(西红柿,茄子)',1,null,null UNION ALL
SELECT '(辣椒,茄子)',-1,null,null UNION ALL
SELECT '(葡萄,茄子)',0,null,null UNION ALL
SELECT '(苹果,茄子)',1,null,null
Select * from T1
select value,name1=substring(value,2,charindex(',',value)-2),name2=substring(value,charindex(',',value)+1,len(value)-charindex(',',value)-1)
into #1
from T1create table #2(value varchar(20),num int,num1 int,num2 int)
declare @sql nvarchar(max)
select @sql=isnull(@sql+' union all ','insert #2 ')+' select '''+value+''',num=(select count(*) from Tongji where '+name1+'=1 and '+name2
+'=1),num1=(select sum('+name1+') from Tongji),num2=(select sum('+name2+') from Tongji)'
from #1exec(@sql)declare @p decimal(18,3)
set @p=0.5 --在此设置百分比参数update a
set status1=case when b.p1>=@p then '高级' else '低级' end
,status2=case when b.p2>=@p then '高级' else '低级' end
from T1 a
join (select value,p1=num*1.0/num1,p2=num*1.0/num2 from #2) b
on a.value=b.value
where a.flag<>0select * from T1
/*
value flag status1 status2
-------------------- ----------- ---------- ----------
(西红柿,辣椒) -1 高级 高级
(西红柿,葡萄) 0 NULL NULL
(辣椒,葡萄) -1 高级 高级
(西红柿,苹果) -1 低级 高级
(辣椒,苹果) 1 低级 高级
(葡萄,苹果) -1 高级 高级
(西红柿,茄子) 1 高级 高级
(辣椒,茄子) -1 高级 高级
(葡萄,茄子) 0 NULL NULL
(苹果,茄子) 1 高级 低级(10 行受影响)
*/
drop table #1
drop table #2
update a
set status1=case when b.p1>=@p then '高级' else '低级' end
,status2=case when b.p2>=@p then '高级' else '低级' end
from T1 a
join (select value,p1=num*1.0/nullif(num1,0),p2=num*1.0/nullif(num2,0) from #2) b
on a.value=b.value
where a.flag<>0
value flag status1 status2
(西红柿,辣椒) -1 高级 高级 对应高级值(num/num1):3/5=0.6 对应高级值(num/num2):3/6=0.5
(西红柿,葡萄) 0 NULL NULL
(辣椒,葡萄) -1 高级 高级 对应高级值(num/num1):3/6=0.5 对应高级值(num/num2):3/3=1
(西红柿,苹果) -1 高级 高级 对应高级值(num/num1):5/5=1 对应高级值(num/num2):5/2=2.5
(辣椒,苹果) 1 低级 低级 对应高级值(num/num1):2/5=0.4 对应高级值(num/num2):2/6=0.33
(葡萄,苹果) -1 高级 高级
(西红柿,茄子) 1 高级 高级
(辣椒,茄子) -1 低级 高级
(葡萄,茄子) 0 NULL NULL
(苹果,茄子) 1 高级 低级
value flag status1 status2
(西红柿,辣椒) -1 高级 高级 对应高级值(num/num1):3/5=0.6 对应高级值(num/num2):3/6=0.5
(西红柿,葡萄) 0 NULL NULL
(辣椒,葡萄) -1 高级 高级 对应高级值(num/num1):3/6=0.5 对应高级值(num/num2):3/3=1
(西红柿,苹果) -1 高级 高级 对应高级值(num/num1):5/5=1 对应高级值(num/num2):5/2=2.5
(辣椒,苹果) 1 低级 低级 对应高级值(num/num1):2/5=0.4 对应高级值(num/num2):2/6=0.33
(葡萄,苹果) -1 低级 高级 对应高级值(num/num1):1/3=0.3 对应高级值(num/num2):1/2=0.5
(西红柿,茄子) 1 高级 高级 对应高级值(num/num1):3/5=0.6 对应高级值(num/num2):3/3=1
(辣椒,茄子) -1 高级 高级 对应高级值(num/num1):3/6=0.5 对应高级值(num/num2):3/3=1
(葡萄,茄子) 0 NULL NULL
(苹果,茄子) 1 高级 低级 对应高级值(num/num1):1/2=0.5 对应高级值(num/num2):1/3=0.3
EXEC MASTER.dbo.xp_execresultset N'select ''UPDATE T1 SET status1 = CASE WHEN '' +
''(SELECT COUNT(1) FROM Tongji WHERE '' + left(value,charindex('','',value)-1) +
'' = 1))*0.5 <= (SELECT COUNT(1) FROM Tongji WHERE '' + replace(replace(replace(value,''('',''''),'')'',''''),'','','' = 1 AND '') + '' = 1)'' +
'' THEN ''''高级'''' ELSE ''''低级'''' END,status2 = CASE WHEN '' +
''(SELECT COUNT(1) FROM Tongji WHERE ('' + substring(value,charindex('','',value)+1,len(value)) +
'' = 1)*0.5 <= (SELECT COUNT(1) FROM Tongji WHERE '' + replace(replace(replace(value,''('',''''),'')'',''''),'','','' = 1 AND '') + '' = 1)'' +
'' THEN ''''高级'''' ELSE ''''低级'''' END WHERE flag = 1 and value = '''''' + value + '''''''' from T1 where flag = 1 ',N'tempdb'EXEC MASTER.dbo.xp_execresultset N'select ''UPDATE T1 SET status1 = CASE WHEN '' +
''(SELECT COUNT(1) FROM Tongji WHERE '' + left(value,charindex('','',value)-1) +
'' = 1))*0.5 <= (SELECT COUNT(1) FROM Tongji WHERE ('' + replace(replace(replace(value,''('',''''),'')'',''''),'','','' = 1 AND '') + '' = 0) OR ('' + replace(replace(replace(value,''('',''''),'')'',''''),'','','' = 0 AND '') + '' = 1)) '' +
'' THEN ''''高级'''' ELSE ''''低级'''' END,status2 = CASE WHEN '' +
''(SELECT COUNT(1) FROM Tongji WHERE ('' + substring(value,charindex('','',value)+1,len(value)) +
'' = 1)*0.5 <= (SELECT COUNT(1) FROM Tongji WHERE ('' + replace(replace(replace(value,''('',''''),'')'',''''),'','','' = 1 AND '') + '' = 0) OR ('' + replace(replace(replace(value,''('',''''),'')'',''''),'','','' = 0 AND '') + '' = 1)) '' +
'' THEN ''''高级'''' ELSE ''''低级'''' END WHERE flag = -1 and value = '''''' + value + '''''''' from T1 where flag = -1 ',N'tempdb'
楼上的结果消息 2812,级别 16,状态 1,第 1 行
找不到存储过程 'MASTER.dbo.xp_execresultset'。
消息 2812,级别 16,状态 1,第 11 行
找不到存储过程 'MASTER.dbo.xp_execresultset'。
CREATE TABLE Tongji (西红柿 INT,辣椒 INT,葡萄 INT,苹果 INT,茄子 INT)
INSERT INTO Tongji
SELECT 1,0,0,0,0 UNION ALL
SELECT 1,1,0,0,0 UNION ALL
SELECT 0,1,1,1,0 UNION ALL
SELECT 1,1,0,0,1 UNION ALL
SELECT 0,1,1,0,0 UNION ALL
SELECT 1,1,0,0,1 UNION ALL
SELECT 1,1,1,1,1
Select * from Tongji
IF OBJECT_ID('dbo.T1') IS NOT NULL DROP TABLE T1
CREATE TABLE T1 (value varchar(20),flag int,status1 varchar(10),status2 varchar(10))
INSERT INTO T1
SELECT '(西红柿,辣椒)',-1,null,null UNION ALL
SELECT '(西红柿,葡萄)',0,null,null UNION ALL
SELECT '(辣椒,葡萄)',-1,null,null UNION ALL
SELECT '(西红柿,苹果)',-1,null,null UNION ALL
SELECT '(辣椒,苹果)',1,null,null UNION ALL
SELECT '(葡萄,苹果)',-1,null,null UNION ALL
SELECT '(西红柿,茄子)',1,null,null UNION ALL
SELECT '(辣椒,茄子)',-1,null,null UNION ALL
SELECT '(葡萄,茄子)',0,null,null UNION ALL
SELECT '(苹果,茄子)',1,null,null
Select * from T1
select value,name1=substring(value,2,charindex(',',value)-2),name2=substring(value,charindex(',',value)+1,len(value)-charindex(',',value)-1),flag
into #1
from T1
where flag<>0create table #2(value varchar(20),num int,num1 int,num2 int)
declare @sql nvarchar(max)
select @sql=isnull(@sql+' union all ','insert #2 ')+' select '''+value
+''',num='+(case flag when 1 then ('(select count(*) from Tongji where '+name1+'=1 and '+name2+'=1)') when -1 then ('(select count(*) from Tongji where '+name1+'+'+name2+'=1)') end)
+',num1=(select sum('+name1+') from Tongji),num2=(select sum('+name2+') from Tongji)'
from #1
print @sql
exec(@sql)declare @p decimal(18,3)
set @p=0.5 --在此设置百分比参数update a
set status1=case when b.p1>=@p then '高级' else '低级' end
,status2=case when b.p2>=@p then '高级' else '低级' end
from T1 a
join (select value,p1=num*1.0/nullif(num1,0),p2=num*1.0/nullif(num2,0) from #2) b
on a.value=b.valueselect * from T1
/*
value flag status1 status2
-------------------- ----------- ---------- ----------
(西红柿,辣椒) -1 高级 高级
(西红柿,葡萄) 0 NULL NULL
(辣椒,葡萄) -1 高级 高级
(西红柿,苹果) -1 高级 高级
(辣椒,苹果) 1 低级 高级
(葡萄,苹果) -1 低级 高级
(西红柿,茄子) 1 高级 高级
(辣椒,茄子) -1 高级 高级
(葡萄,茄子) 0 NULL NULL
(苹果,茄子) 1 高级 低级(10 行受影响)
*/
drop table #1
drop table #2
select 'UPDATE T1 SET status1 = CASE WHEN ' +
'(SELECT COUNT(1) FROM Tongji WHERE ' + left(value,charindex(',',value)-1) +
' = 1))*0.5 <= (SELECT COUNT(1) FROM Tongji WHERE ' + replace(replace(replace(value,'(',''),')',''),',',' = 1 AND ') + ' = 1)' +
' THEN ''高级'' ELSE ''低级'' END,status2 = CASE WHEN ' +
'(SELECT COUNT(1) FROM Tongji WHERE (' + substring(value,charindex(',',value)+1,len(value)) +
' = 1)*0.5 <= (SELECT COUNT(1) FROM Tongji WHERE ' + replace(replace(replace(value,'(',''),')',''),',',' = 1 AND ') + ' = 1)' +
' THEN ''高级'' ELSE ''低级'' END WHERE flag = 1 and value = ''' + value + '''' from T1 where flag = 1 insert into #tb(sql)
select 'UPDATE T1 SET status1 = CASE WHEN ' +
'(SELECT COUNT(1) FROM Tongji WHERE ' + left(value,charindex(',',value)-1) +
' = 1))*0.5 <= (SELECT COUNT(1) FROM Tongji WHERE (' + replace(replace(replace(value,'(',''),')',''),',',' = 1 AND ') + ' = 0) OR (' + replace(replace(replace(value,'(',''),')',''),',',' = 0 AND ') + ' = 1)) ' +
' THEN ''高级'' ELSE ''低级'' END,status2 = CASE WHEN ' +
'(SELECT COUNT(1) FROM Tongji WHERE (' + substring(value,charindex(',',value)+1,len(value)) +
' = 1)*0.5 <= (SELECT COUNT(1) FROM Tongji WHERE (' + replace(replace(replace(value,'(',''),')',''),',',' = 1 AND ') + ' = 0) OR (' + replace(replace(replace(value,'(',''),')',''),',',' = 0 AND ') + ' = 1)) ' +
' THEN ''高级'' ELSE ''低级'' END WHERE flag = -1 and value = ''' + value + '''' from T1 where flag = -1 declare @i int,@sql varchar(8000)
set @i = 1
while @i <= (select max(ID) from #tb)
begin
select @sql = sql from #tb where ID = @i
exec(@sql)
set @i = @i + 1
end