视图中的where/case 如何写

[Dision LI]

SELECT  tblwfsqd.*,
        (SELECT MIN(tblbjcpjjb.shdj)
         FROM   tblbjcpjjb
                LEFT OUTER JOIN   客户 ON tblbjcpjjb.bmid=客户.客户ID
         WHERE  (tblbjcpjjb.cpid=tblwfsqd.货号) AND (tblbjcpjjb.gxid=tblwfsqd.工序ID)

      --   AND (CASE WHEN  客户.类型='品牌' then  tblbjcpjjb.bmid  in ( 174,221)
      --   else tblbjcpjjb.bmid not in ( 174,221) end)  

        ) AS 最低价
FROM    tblwfsqd

[AcHerat]

SELECT  tblwfsqd.*,
        (SELECT MIN(tblbjcpjjb.shdj)
         FROM   tblbjcpjjb
                LEFT OUTER JOIN   客户 ON tblbjcpjjb.bmid=客户.客户ID
         WHERE  (tblbjcpjjb.cpid=tblwfsqd.货号) AND (tblbjcpjjb.gxid=tblwfsqd.工序ID)

         AND ((客户.类型=N'品牌' and  tblbjcpjjb.bmid  in ( 174,221))
               or tblbjcpjjb.bmid not in ( 174,221))  

        ) AS 最低价
FROM    tblwfsqd

[Dision LI]

思想僵化了。。。。感谢AcHerat

[Shawn]

SELECT  tblwfsqd.*,
        (SELECT MIN(tblbjcpjjb.shdj)
         FROM   tblbjcpjjb
                LEFT OUTER JOIN   ¿Í»§ ON tblbjcpjjb.bmid=¿Í»§.¿Í»§ID
         WHERE  (tblbjcpjjb.cpid=tblwfsqd.»õºÅ) AND (tblbjcpjjb.gxid=tblwfsqd.¹¤ÐòID)
		 AND
		 (
		 	(¿Í»§.ÀàÐÍ='Æ·ÅÆ' AND tblbjcpjjb.bmid  in ( 174,221)) OR
		 	(¿Í»§.ÀàÐÍ<>'Æ·ÅÆ' AND tblbjcpjjb.bmid not in ( 174,221))
		 )
      --   AND (CASE WHEN  ¿Í»§.ÀàÐÍ='Æ·ÅÆ' then  tblbjcpjjb.bmid  in ( 174,221)
      --   else tblbjcpjjb.bmid not in ( 174,221) end)  

        ) AS ×îµÍ¼Û
FROM    tblwfsqd

[jxqn_liu]

SELECT  tblwfsqd.*,
        (SELECT MIN(tblbjcpjjb.shdj)
         FROM   tblbjcpjjb
                LEFT OUTER JOIN   客户 ON tblbjcpjjb.bmid=客户.客户ID
         WHERE  (tblbjcpjjb.cpid=tblwfsqd.货号) AND (tblbjcpjjb.gxid=tblwfsqd.工序ID)
           And ((客户.类型='品牌' and tblbjcpjjb.bmid  in ( 174,221)) or (客户.类型<>'品牌' and tblbjcpjjb.bmid not in ( 174,221)))

        ) AS 最低价
FROM    tblwfsqd

[Shawn]

SELECT  tblwfsqd.*,
        (SELECT MIN(tblbjcpjjb.shdj)
         FROM   tblbjcpjjb
                LEFT OUTER JOIN   客户 ON tblbjcpjjb.bmid=客户.客户ID
         WHERE  (tblbjcpjjb.cpid=tblwfsqd.货号) AND (tblbjcpjjb.gxid=tblwfsqd.工序ID)
				 AND
				 (
					 	(客户.类型='品牌'  AND tblbjcpjjb.bmid  in ( 174,221)) OR
					 	((客户.类型<>'品牌' OR 客户.类型 IS NULL)  AND tblbjcpjjb.bmid not in ( 174,221))
				 )

        ) AS 最低价
FROM  tblwfsqd

[打一壶酱油]

[Quote=引用 5 楼 wwwwgou 的回复:]
SQL code
SELECT tblwfsqd.*,
(SELECT MIN(tblbjcpjjb.shdj)
FROM tblbjcpjjb
LEFT OUTER JOIN 客户 ON tblbjcpjjb.bmid=客户.客户ID
WHERE (tblbjcpjjb.cpid=tblwf……
[/Quote]

+++