if条件为sql查询 [英] if condition for sql query

问题描述

if（a.WH_NO = b.WH_NO）



选择a.PRODUCT_NO，b.PRODUCT_NAME，a.QTY_IN，b.QTY_OUT，a.UNIT_COST FROM STOCKDETAILS a，STOCKDETAILS b WHERE.PRODUCT_NO = b.PRODUCT_NO和a.WH_NO ='WH1'和a.STOCK_JR ='IN'和b.STOCK_JR ='OU'



if（a.WH_NO！= b.WH_NO）



SELECT a.PRODUCT_NO，b.PRODUCT_NAME，a.QTY_IN，a.UNIT_COST FROM STOCKDETAILS a， STOCKDETAILS b WHERE.PRODUCT_NO = b.PRODUCT_NO和a.WH_NO ='WH1'和a.STOCK_JR ='IN'和b.STOCK_JR ='OU'



i希望这些查询在单个结果集上执行

解决方案

首先，要将这些查询转换为单个结果集，您需要两个查询在结果中返回相同数量的列。 br />


案例 [ ^ ]是您需要的构造，而非IF



试试这个（未经测试）

  SELECT  a.PRODUCT_NO ，b.PRODUCT_NAME，a.QTY_IN，
 QTY_OUT =  CASE   WHEN  a.WH_NO = b .WH_NO 那么 b.QTY_OUT  ELSE   0   END  
，a.UNIT_COST 
  FROM  STOCKDETAILS a，
 < span class =code-keyword> INNER   JOIN  STOCKDETAILS b  ON  a.PRODUCT_NO = b.PRODUCT_NO  AND  b.STOCK_JR = '  OU' 
  WHERE  a.WH_NO = '  WH1'  AND  a.STOCK_JR = '  IN' 

if(a.WH_NO=b.WH_NO)

SELECT a.PRODUCT_NO,b.PRODUCT_NAME,a.QTY_IN,b.QTY_OUT,a.UNIT_COST FROM STOCKDETAILS a, STOCKDETAILS b WHERE a.PRODUCT_NO=b.PRODUCT_NO and a.WH_NO='WH1' AND a.STOCK_JR='IN' AND b.STOCK_JR='OU'

if(a.WH_NO!=b.WH_NO)

SELECT a.PRODUCT_NO,b.PRODUCT_NAME,a.QTY_IN,a.UNIT_COST FROM STOCKDETAILS a, STOCKDETAILS b WHERE a.PRODUCT_NO=b.PRODUCT_NO and a.WH_NO='WH1' AND a.STOCK_JR='IN' AND b.STOCK_JR='OU'

i want these queries execute at single result set

解决方案

Firstly to get these into a single result set you need both queries to return the same number of columns in the result.

CASE [^]is the construct you need, not IF

Try this (untested)

SELECT a.PRODUCT_NO,b.PRODUCT_NAME,a.QTY_IN,
QTY_OUT = CASE WHEN a.WH_NO=b.WH_NO THEN b.QTY_OUT ELSE 0 END
,a.UNIT_COST
FROM STOCKDETAILS a,
INNER JOIN STOCKDETAILS b ON a.PRODUCT_NO=b.PRODUCT_NO AND b.STOCK_JR='OU'
WHERE a.WH_NO='WH1' AND a.STOCK_JR='IN'

这篇关于if条件为sql查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！