这是我写的程序。结果是正确的。
但算法我想不是最好。 不用存储过程来做。我用了临时表。可以不用临时表吗?
SELECT *
INTO #table1
FROM (SELECT DISTINCT khbh
FROM xskd
WHERE datediff(day, xkdate, getdate()) > skjx) b
SELECT khbh, type AS 类型, xkdh AS 单号, Summoney AS 欠款金额,
OrderType
FROM (SELECT khbh, '开单' AS type, xkdh AS xkdh, Summoney AS Summoney,
2 AS OrderType
FROM xskd
WHERE datediff(day, xkdate, getdate()) > skjx
UNION ALL
SELECT khbh, '小计' AS type, NULL AS xkdh, SUM(summoney)
AS Summoney, 4 AS OrderType
FROM (SELECT xskd.khbh, SUM(isnull(summoney, 0)) AS summoney
FROM xskd
WHERE datediff(day, xkdate, getdate()) > skjx
GROUP BY xskd.khbh) AS a
GROUP BY khbh
UNION ALL
SELECT khbh, khname AS type, NULL AS xkdh, NULL AS summoney,
1 AS OrderType
FROM ytkhzlb
WHERE khbh IN
(SELECT khbh
FROM #table1)
UNION ALL
SELECT khbh, NULL AS type, NULL AS xkdh, NULL AS summoney,
5 AS OrderType
FROM ytkhzlb
WHERE khbh IN
(SELECT khbh
FROM #table1)) AS t
ORDER BY khbh, OrderType
SELECT *
FROM #table1
drop table #table1
但算法我想不是最好。 不用存储过程来做。我用了临时表。可以不用临时表吗?
SELECT *
INTO #table1
FROM (SELECT DISTINCT khbh
FROM xskd
WHERE datediff(day, xkdate, getdate()) > skjx) b
SELECT khbh, type AS 类型, xkdh AS 单号, Summoney AS 欠款金额,
OrderType
FROM (SELECT khbh, '开单' AS type, xkdh AS xkdh, Summoney AS Summoney,
2 AS OrderType
FROM xskd
WHERE datediff(day, xkdate, getdate()) > skjx
UNION ALL
SELECT khbh, '小计' AS type, NULL AS xkdh, SUM(summoney)
AS Summoney, 4 AS OrderType
FROM (SELECT xskd.khbh, SUM(isnull(summoney, 0)) AS summoney
FROM xskd
WHERE datediff(day, xkdate, getdate()) > skjx
GROUP BY xskd.khbh) AS a
GROUP BY khbh
UNION ALL
SELECT khbh, khname AS type, NULL AS xkdh, NULL AS summoney,
1 AS OrderType
FROM ytkhzlb
WHERE khbh IN
(SELECT khbh
FROM #table1)
UNION ALL
SELECT khbh, NULL AS type, NULL AS xkdh, NULL AS summoney,
5 AS OrderType
FROM ytkhzlb
WHERE khbh IN
(SELECT khbh
FROM #table1)) AS t
ORDER BY khbh, OrderType
SELECT *
FROM #table1
drop table #table1
还忘说,这个问题是计算超期出超出给定的时间,提示用户应该收款的作用。
也就是超期应收款.skjx也是xskd表里的。int类型 xkdate是datetime类型。WHERE datediff(day, xkdate, getdate()) > skjx
这句是计算有没有超期.
大家不要误解意思。我最终要实现的目标就是上面要的结果.
我上面的算法结果一定是对的。但有更高效的算法没有
2、在查询条件后面加个where khbh not is null
根定这样不行的。