现有两表: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.   


    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
      

  2.   

    修改一下update语句,以避免除0错。
    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
      

  3.   

    依据创建的两表及说明,需下面的结果,给定的百分比 @p=0.5 T1表(更新后) 
    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   高级 低级 
      

  4.   

    依据创建的两表及说明,需下面的结果,给定的百分比 @p=0.5 T1表(更新后对比值) 
    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
      

  5.   

    上面所有flag为1值的结果是对的,flag为-1的算法是我后加的!第四行,(西红柿,苹果) 当flag为-1时,num取两列为0,1或1,0的情况,但不能同时为1或0,其余num1,num2取法相同
      

  6.   


    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'
      

  7.   


    楼上的结果消息 2812,级别 16,状态 1,第 1 行
    找不到存储过程 'MASTER.dbo.xp_execresultset'。
    消息 2812,级别 16,状态 1,第 11 行
    找不到存储过程 'MASTER.dbo.xp_execresultset'。
      

  8.   

    IF OBJECT_ID('dbo.Tongji') IS NOT NULL DROP TABLE Tongji 
    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
      

  9.   

    修改一下:CREATE TABLE #tb(ID int identity(1,1),sql varchar(8000))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 ') + ' = 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