select Insert_SingleNumber as 所属订单,Insert_GoodsName as 货物名称,Insert_GoodsModel as 规格型号,Insert_GoodsUnit as 单位,Insert_GoodsPrice as 单价,Insert_GoodsAmount as 数量 from MIS_InsertInfo where Insert_SingleNumber=(select Single_Number from MIS_Single where(Single_Number=321）
这里一共提到了2张表，MIS_InsertInfo（订单的详细信息表）和MIS_Single（订单表，只包括订单日期和编号），我想查出订单表里编号所对应的信息表的详细信息，因为Single_Number=321所对应的肯定不止一条记录，那该怎么改这个句子呢
------解决方案--------------------
select Insert_SingleNumber as 所属订单,Insert_GoodsName as 货物名称,Insert_GoodsModel as 规格型号,Insert_GoodsUnit as 单位,Insert_GoodsPrice as 单价,Insert_GoodsAmount as 数量 from MIS_InsertInfo where Insert_SingleNumber in(select Single_Number from MIS_Single where Single_Number=321)
用in就好
------解决方案--------------------
- SQL code
select Insert_SingleNumber as 所属订单,Insert_GoodsName as 货物名称,Insert_GoodsModel as 规格型号,Insert_GoodsUnit as 单位,Insert_GoodsPrice as 单价,Insert_GoodsAmount as 数量 from MIS_InsertInfo , MIS_Singlewhere InsertInfo.Insert_SingleNumber = MIS_Single.Single_Number and MIS_Single.Single_Number=321
------解决方案--------------------
貌似你后面那个表没什么用处,可直接更改为如下:
- SQL code
select Insert_SingleNumber as 所属订单,Insert_GoodsName as 货物名称,Insert_GoodsModel as 规格型号,Insert_GoodsUnit as 单位,Insert_GoodsPrice as 单价,Insert_GoodsAmount as 数量 from MIS_InsertInfo where Insert_SingleNumber = 321
------解决方案--------------------
in 的效率比较低，大数据量的话查询很慢
建议用exists
- SQL code
select Insert_SingleNumber as 所属订单,Insert_GoodsName as 货物名称,Insert_GoodsModel as 规格型号,Insert_GoodsUnit as 单位,Insert_GoodsPrice as 单价,Insert_GoodsAmount as 数量 from MIS_InsertInfo where exists(select Single_Number from MIS_Single where Single_Number=321 and MIS_InsertInfo.Insert_SingleNumber = MIS_Single.Single_Number)