知识点
判断是否存在临时表,存在的话,删除
IF OBJECT_ID('TEMPDB..#KSXSD') IS NOT NULL
DROP TABLE #KSXSD
---这里写具体代码----
GO
问题描述:
之前调用的数据调用存储,存储里只能查询一天的数据。现在的需求要求查询时间段的数据。打开存储后发现 我需要的数据就是那些,只不过时间改成时间段。为了省事,直接把存储改成表,进行查询,之前的存储写的很清楚 调用临时表,再去进行条件过滤。
```bash
IF OBJECT_ID('TEMPDB..#KSXSD') IS NOT NULL
DROP TABLE #KSXSD,#DLTGF,#KHHL,#PMB,#KXXSD,#KSTHD,#KXTHD,#FCPZL
----------------------品名表
CREATE TABLE #PMB (PM VARCHAR(30) )
INSERT INTO #PMB( PM )
VALUES ( '18K素金'),('22K素金'),('18K镶嵌'),('千足金'),('其他')
------------------素金销售 结价------------
SELECT PM,SJXSJZ,SJXSGF INTO #KSXSD FROM(
SELECT PM=CASE WHEN CSMC='18K' THEN '18K素金' ELSE '22K素金' END,
SJXSJZ=SUM(ISNULL(JJJZ,0)),SJXSGF=SUM(ISNULL(JJJE,0))
FROM dbo.KJXSDH WHERE PLMCH='K素' AND RQ>='${开始日期}' AND RQ<='${截止日期}'
GROUP BY CSMC
UNION ALL
SELECT PM='其他',SJXSJZ=0,SJXSGF=SUM(SJXSGF) from(
SELECT SJXSGF=SUM(ISNULL(FJGF,0))
FROM dbo.KJXSDH WHERE PLMCH='K素' AND RQ>='${开始日期}' AND RQ<='${截止日期}'
UNION ALL
SELECT SJXSGF=SUM(ISNULL(JEF,0))
FROM KJXSDH,dbo.KJXSDF
WHERE KJXSDH.DjLsh=KJXSDF.DjLsh AND (JSFS='邮寄费' OR JSFS='保价费' OR JSFS='其它' ) AND RQ>='${开始日期}' AND RQ<='${截止日期}'
)B
)A
------------------镶嵌销售------------
SELECT PM=CASE WHEN CSMC='18K' THEN '18K镶嵌' ELSE '22K镶嵌' END,
XQXSJS=SUM(ISNULL(JSHJ,0)),XQXSZHBQJE=SUM(ISNULL(ZJEHJ,0))
INTO #KXXSD FROM dbo.KJXSDH WHERE PLMCH='K镶' AND RQ>='${开始日期}' AND RQ<='${截止日期}'
GROUP BY CSMC
----------------------对料贴工费-------------------------------
/*销售单来料金重*/
SELECT PM=CASE WHEN CSMC='18K' THEN '18K素金' ELSE '22K素金' END,
TGFZL=SUM(ISNULL(LLJZ,0)),TGFGF=SUM(ISNULL(LLGFHJ,0))
INTO #DLTGF FROM dbo.KJXSDH WHERE PLMCH='K素' AND RQ>='${开始日期}' AND RQ<='${截止日期}'
GROUP BY CSMC
----------------------客户还料---------------------------------
/*收料单未调用*/
SELECT PM=CASE WHEN LLCSMCB='18K' THEN '18K素金'
WHEN LLCSMCB='22K' THEN '22K素金'
WHEN LLCSMCB='千足金' THEN '千足金' END,
KHHLZL=SUM(ISNULL(JZB,0)),KHHLGF=SUM(ISNULL(JE,0))
INTO #KHHL FROM dbo.KJSLDH,KJSLDB
WHERE KJSLDH.DjLsh=KJSLDB.DjLsh AND ISNULL(SYZL,0)=JZHJ AND SWRQ='2021-06-10' AND ISNULL(GYS,0)<>1
GROUP BY LLCSMCB
----------------------客户退饰---------------------------------
/*K素退货单*/
SELECT PM=CASE WHEN CSMC='18K' THEN '18K素金'
WHEN CSMC='22K' THEN '22K素金'
WHEN CSMC='千足金' THEN '其他' END,
KSTHJZ=SUM(ISNULL(TSJZ,0)),KSTHGF=SUM(ISNULL(ZJE,0))
INTO #KSTHD FROM dbo.KJTHDH,dbo.KJTHDB
WHERE KJTHDH.DJLSH=KJTHDB.DJLSH AND LX='K素' AND RQ>='${开始日期}' AND RQ<='${截止日期}'
GROUP BY CSMC
/*K镶退货单*/
SELECT PM=CASE WHEN CSMC='18K' THEN '18K镶嵌'
WHEN CSMC='22K' THEN '22K镶嵌'
WHEN CSMC='千足金' THEN '其他' END,
KXTHJS=SUM(ISNULL(TSJS,0)),KXTHZHBQJE=SUM(ISNULL(ZHJE,0))
INTO #KXTHD FROM dbo.KJTHDH,dbo.KJTHDB
WHERE KJTHDH.DJLSH=KJTHDB.DJLSH AND LX='K镶' AND RQ>='${开始日期}' AND RQ<='${截止日期}'
GROUP BY CSMC
----------------------发出成品重量---------------------------------
SELECT PM=CASE WHEN CSMC='18K' THEN '18K素金' ELSE '22K素金' END,
CPZL=SUM(ISNULL(ZQJZ,0)) INTO #FCPZL FROM KJXSDH
WHERE RQ>='${开始日期}' AND RQ<='${截止日期}'
GROUP BY CSMC
----------------------表结合查询数据显示-----------------------
SELECT #PMB.PM,SJXSJZ,SJXSGF,
XQXSJS,XQXSZHBQJE,
TGFZL,TGFGF,
KHHLZL,KHHLGF,
KSTHJZ,KSTHGF,
KXTHJS,KXTHZHBQJE,
CPZL,
JSHJ=ISNULL(XQXSJS,0)-ISNULL(KXTHJS,0),
ZLHJ=ISNULL(SJXSJZ,0)+ISNULL(TGFZL,0)+ISNULL(KHHLZL,0)-ISNULL(KSTHJZ,0)+ISNULL(CPZL,0),
JEHJ=ISNULL(SJXSGF,0)+ISNULL(XQXSZHBQJE,0)+ISNULL(TGFGF,0)+ISNULL(KHHLGF,0)-ISNULL(KSTHGF,0)-ISNULL(KXTHZHBQJE,0)
FROM #PMB
LEFT JOIN #KSXSD ON #PMB.PM=#KSXSD.PM
LEFT JOIN #KXXSD ON #PMB.PM=#KXXSD.PM
LEFT JOIN #DLTGF ON #PMB.PM=#DLTGF.PM
LEFT JOIN #KHHL ON #PMB.PM=#KHHL.PM
LEFT JOIN #KSTHD ON #PMB.PM=#KSTHD.PM
LEFT JOIN #KXTHD ON #PMB.PM=#KXTHD.PM
LEFT JOIN #FCPZL ON #PMB.PM=#FCPZL.PM
然后,我修改了一下
```bash
IF OBJECT_ID('TEMPDB..#KSXSD') IS NOT NULL
DROP TABLE #KSXSD,#DLTGF,#KHHL,#PMB,#KXXSD,#KSTHD,#KXTHD,#FCPZL
----------------------品名表
CREATE TABLE #PMB (PM VARCHAR(30) )
INSERT INTO #PMB( PM )
VALUES ( '18K素金'),('22K素金'),('18K镶嵌'),('千足金'),('其他')
------------------素金销售 结价------------
SELECT PM,SJXSJZ,SJXSGF INTO #KSXSD FROM(
SELECT PM=CASE WHEN CSMC='18K' THEN '18K素金' ELSE '22K素金' END,
SJXSJZ=SUM(ISNULL(JJJZ,0)),SJXSGF=SUM(ISNULL(JJJE,0))
FROM dbo.KJXSDH WHERE PLMCH='K素' AND RQ>='2021-06-10' AND RQ<='2021-06-11'
GROUP BY CSMC
UNION ALL
SELECT PM='其他',SJXSJZ=0,SJXSGF=SUM(SJXSGF) from(
SELECT SJXSGF=SUM(ISNULL(FJGF,0))
FROM dbo.KJXSDH WHERE PLMCH='K素' AND RQ>='2021-06-10' AND RQ<='2021-06-11'
UNION ALL
SELECT SJXSGF=SUM(ISNULL(JEF,0))
FROM KJXSDH,dbo.KJXSDF
WHERE KJXSDH.DjLsh=KJXSDF.DjLsh AND (JSFS='邮寄费' OR JSFS='保价费' OR JSFS='其它' ) AND RQ>='2021-06-10' AND RQ<='2021-06-11'
)B
)A
------------------镶嵌销售------------
SELECT PM=CASE WHEN CSMC='18K' THEN '18K镶嵌' ELSE '22K镶嵌' END,
XQXSJS=SUM(ISNULL(JSHJ,0)),XQXSZHBQJE=SUM(ISNULL(ZJEHJ,0))
INTO #KXXSD FROM dbo.KJXSDH WHERE PLMCH='K镶' AND RQ>='2021-06-10' AND RQ<='2021-06-11'
GROUP BY CSMC
----------------------对料贴工费-------------------------------
/*销售单来料金重*/
SELECT PM=CASE WHEN CSMC='18K' THEN '18K素金' ELSE '22K素金' END,
TGFZL=SUM(ISNULL(LLJZ,0)),TGFGF=SUM(ISNULL(LLGFHJ,0))
INTO #DLTGF FROM dbo.KJXSDH WHERE PLMCH='K素' AND RQ>='2021-06-10' AND RQ<='2021-06-11'
GROUP BY CSMC
----------------------客户还料---------------------------------
/*收料单未调用*/
SELECT PM=CASE WHEN LLCSMCB='18K' THEN '18K素金'
WHEN LLCSMCB='22K' THEN '22K素金'
WHEN LLCSMCB='千足金' THEN '千足金' END,
KHHLZL=SUM(ISNULL(JZB,0)),KHHLGF=SUM(ISNULL(JE,0))
INTO #KHHL FROM dbo.KJSLDH,KJSLDB
WHERE KJSLDH.DjLsh=KJSLDB.DjLsh AND ISNULL(SYZL,0)=JZHJ AND SWRQ='2021-06-10' AND ISNULL(GYS,0)<>1
GROUP BY LLCSMCB
----------------------客户退饰---------------------------------
/*K素退货单*/
SELECT PM=CASE WHEN CSMC='18K' THEN '18K素金'
WHEN CSMC='22K' THEN '22K素金'
WHEN CSMC='千足金' THEN '其他' END,
KSTHJZ=SUM(ISNULL(TSJZ,0)),KSTHGF=SUM(ISNULL(ZJE,0))
INTO #KSTHD FROM dbo.KJTHDH,dbo.KJTHDB
WHERE KJTHDH.DJLSH=KJTHDB.DJLSH AND LX='K素' AND RQ>='2021-06-10' AND RQ<='2021-06-11'
GROUP BY CSMC
/*K镶退货单*/
SELECT PM=CASE WHEN CSMC='18K' THEN '18K镶嵌'
WHEN CSMC='22K' THEN '22K镶嵌'
WHEN CSMC='千足金' THEN '其他' END,
KXTHJS=SUM(ISNULL(TSJS,0)),KXTHZHBQJE=SUM(ISNULL(ZHJE,0))
INTO #KXTHD FROM dbo.KJTHDH,dbo.KJTHDB
WHERE KJTHDH.DJLSH=KJTHDB.DJLSH AND LX='K镶' AND RQ>='2021-06-10' AND RQ<='2021-06-11'
GROUP BY CSMC
----------------------发出成品重量---------------------------------
SELECT PM=CASE WHEN CSMC='18K' THEN '18K素金' ELSE '22K素金' END,
CPZL=SUM(ISNULL(ZQJZ,0)) INTO #FCPZL FROM KJXSDH
WHERE RQ>='2021-06-10' AND RQ<='2021-06-11'
GROUP BY CSMC
----------------------表结合查询数据显示-----------------------
SELECT #PMB.PM,SJXSJZ,SJXSGF,
XQXSJS,XQXSZHBQJE,
TGFZL,TGFGF,
KHHLZL,KHHLGF,
KSTHJZ,KSTHGF,
KXTHJS,KXTHZHBQJE,
CPZL,
JSHJ=ISNULL(XQXSJS,0)-ISNULL(KXTHJS,0),
ZLHJ=ISNULL(SJXSJZ,0)+ISNULL(TGFZL,0)+ISNULL(KHHLZL,0)-ISNULL(KSTHJZ,0)+ISNULL(CPZL,0),
JEHJ=ISNULL(SJXSGF,0)+ISNULL(XQXSZHBQJE,0)+ISNULL(TGFGF,0)+ISNULL(KHHLGF,0)-ISNULL(KSTHGF,0)-ISNULL(KXTHZHBQJE,0)
FROM #PMB
LEFT JOIN #KSXSD ON #PMB.PM=#KSXSD.PM
LEFT JOIN #KXXSD ON #PMB.PM=#KXXSD.PM
LEFT JOIN #DLTGF ON #PMB.PM=#DLTGF.PM
LEFT JOIN #KHHL ON #PMB.PM=#KHHL.PM
LEFT JOIN #KSTHD ON #PMB.PM=#KSTHD.PM
LEFT JOIN #KXTHD ON #PMB.PM=#KXTHD.PM
LEFT JOIN #FCPZL ON #PMB.PM=#FCPZL.PM
GO
这里判断一下,使用IF OBJECT_ID('TEMPDB..#KSXSD') IS NOT NULL DROP TABLE #KSXSD,#DLTGF,#KHHL,#PMB,#KXXSD,#KSTHD,#KXTHD
判断是否存在这个表,不存在的话,则删除。就可以查询出来了