福州某火锅城餐饮收费系统查询代码

福州某火锅城餐饮收费系统查询代码

SELECT consumption.consumptionid AS 消费单号,
consumption.checkindatetime AS 日始时间, consumption.begindatetime AS 开台时间,
consumption.enddatetime AS 结帐时间, consumption.number AS 用餐人数,
emp.empdes AS 开台员工, emp.empdes AS 收银员, site.sitedesc AS 房台
FROM consumption INNER JOIN
emp ON consumption.empid = emp.empid INNER JOIN
site ON consumption.siteid = site.siteid /*消费说明*/

SELECT consumptionid, menuid, SUM(menunumber) AS sum_menunumber
FROM (SELECT consumptionid, menuid, menunumber
FROM consumptionmenu
UNION ALL
SELECT consumptionid, menuid, menunumber
FROM consumption_addmenu) consume
GROUP BY consumptionid, menuid /*消费明细中间代码*/

SELECT group_consume.consumptionid AS 消费单号, group_consume.menuid,
isnull(group_consume.sum_menunumber - consumptioncancel.menunumber,group_consume.sum_menunumber) AS 数量,
menu.menudesc AS 名称, menu.price AS 原价, menu.discount AS 折扣
FROM (SELECT consumptionid, menuid, SUM(menunumber) AS sum_menunumber
FROM (SELECT consumptionid, menuid, menunumber
FROM consumptionmenu
UNION ALL
SELECT consumptionid, menuid, menunumber
FROM consumption_addmenu) consume
GROUP BY consumptionid, menuid) group_consume left outer JOIN
consumptioncancel ON
group_consume.consumptionid = consumptioncancel.consumptionid AND
group_consume.menuid = consumptioncancel.menuid INNER JOIN
menu ON group_consume.menuid = menu.menuid /*消费明细*/

SELECT 消费说明.房台, 消费说明.日始时间, 消费说明.消费单号, 消费说明.用餐人数,
消费说明.开台员工, 消费说明.开台时间, 消费说明.结帐时间, 消费说明.收银员,
消费明细.名称, 消费明细.数量, 消费明细.原价, 消费明细.折扣
FROM (SELECT group_consume.consumptionid AS 消费单号, group_consume.menuid,
isnull(group_consume.sum_menunumber - consumptioncancel.menunumber,group_consume.sum_menunumber) AS 数量,
menu.menudesc AS 名称, menu.price AS 原价, menu.discount AS 折扣
FROM (SELECT consumptionid, menuid, SUM(menunumber) AS sum_menunumber
FROM (SELECT consumptionid, menuid, menunumber
FROM consumptionmenu
UNION ALL
SELECT consumptionid, menuid, menunumber
FROM consumption_addmenu) consume
GROUP BY consumptionid, menuid) group_consume left outer JOIN
consumptioncancel ON
group_consume.consumptionid = consumptioncancel.consumptionid AND
group_consume.menuid = consumptioncancel.menuid INNER JOIN
menu ON group_consume.menuid = menu.menuid) 消费明细 INNER JOIN
(SELECT consumption.consumptionid AS 消费单号,
consumption.checkindatetime AS 日始时间,
consumption.begindatetime AS 开台时间,
consumption.enddatetime AS 结帐时间, consumption.number AS 用餐人数,
emp.empdes AS 开台员工, emp.empdes AS 收银员,
site.sitedesc AS 房台
FROM consumption INNER JOIN
emp ON consumption.empid = emp.empid INNER JOIN
site ON consumption.siteid = site.siteid) 消费说明 ON
消费明细.消费单号 = 消费说明.消费单号 /*消费综合查询*/