sql 的一种复合查询方法 allitem

发布：2015/1/4 13:55:49作者：管理员来源：本站浏览次数：1398

这条语句在多表查询时很有用!

语句如下:

select * from (

select * from 表名

) allitem

order by id

如今天写的一个sql语句:

select INVMC.MC001'品号',INVMB.MB002'品名',INVMB.MB004'单位',SUM(INVMC.MC004)'安全存量 ',SUM(INVMC.MC007)'现有存量',SUM(INVMC.MC004)-SUM(INVMC.MC007)'需补货数量',TD008' 已下单数量'
FROM INVMB JOIN INVMC ON MB001=MC001
JOIN PURTD ON TD004=MC001
WHERE MC002<>'F000' AND MC002<>'H000' AND TD001='331'
GROUP BY MC001,MB002,MB004,TD008
HAVING SUM(INVMC.MC007)<SUM(INVMC.MC004)

union

select * from
(
   select INVMC.MC001'品号',INVMB.MB002'品名',INVMB.MB004'单位',SUM(INVMC.MC004)'安全存量 ',SUM(INVMC.MC007)'现有存量',SUM(INVMC.MC004)-SUM(INVMC.MC007)'需补货数量 ',INVMC.UDF51'已下单数量'
   FROM INVMB JOIN INVMC ON MB001=MC001
   WHERE MC002<>'F000' AND MC002<>'H000'
   GROUP BY MC001,MB002,MB004,INVMC.UDF51
   HAVING SUM(INVMC.MC007)<SUM(INVMC.MC004)
)allitem

where 品号 not in (

select 品号 from

    (
     select INVMC.MC001'品号',INVMB.MB002'品名',INVMB.MB004'单位',SUM(INVMC.MC004)'安全存量 ',SUM(INVMC.MC007)'现有存量',SUM(INVMC.MC004)-SUM(INVMC.MC007)'需补货数量',TD008' 已下单数量'
     FROM INVMB JOIN INVMC ON MB001=MC001
     JOIN PURTD ON TD004=MC001
     WHERE MC002<>'F000' AND MC002<>'H000' AND TD001='331'
     GROUP BY MC001,MB002,MB004,TD008
     HAVING SUM(INVMC.MC007)<SUM(INVMC.MC004)
    )allitem
   )

　PayPal付款页面添加公司标识

SQL Server的排序规则(collation)冲突和解决方案　

outsystems构建sql语句查询过程 sql server 时间转字符串 Sql Server 生成连续时间段 Sql Server 生成连续的日期列表 SQL 生成日期范围间的日期 sql server 获取guid sql server 2014 配置分发 SQL Server删除distribution数据库 power bi 怎么实现一个城市名称一种颜色 sql2014针对FILESTREAM功能被禁用

柏港建站平台

柏港 Mssql

sql 的一种复合查询方法 allitem

柏港为您推荐了一些相关资讯