sql基础语句01

最新推荐文章于 2024-04-28 22:47:38 发布

在路上NEXT

最新推荐文章于 2024-04-28 22:47:38 发布

阅读量913

点赞数

分类专栏： sql笔记文章标签： sql

本文链接：https://blog.csdn.net/qq_33101355/article/details/72934664

版权

sql基础语句笔记
1、将表中某一字段的最大值取出来的方式：

SELECT MAX(contract_no) FROM contract_c WHERE contract_no LIKE '%JK%'

2、在查看合同(contract_c)时，查看合同对应的货物(contract_product_c)数量和货物对应附件(ext_cproduct_c)的数量。
合同表的ID是货物表的外键，货物ID是附件的外键。是两层一对多的关系。

SELECT contract_no,
(
    SELECT COUNT(*) FROM contract_product_c
    WHERE contract_id = c.CONTRACT_ID
)cpnum
FROM contract_c c 
WHERE contract_id='4028817a3357462e01336d3a0be10014'

SELECT COUNT(*) FROM  ext_cproduct_c WHERE contract_product_id IN(
    SELECT contract_product_id FROM contract_product_c 
    WHERE contract_id ='4028817a3357462e01336d3a0be10014'

)

SELECT
(
    SELECT COUNT(*) FROM contract_product_c WHERE contract_id = c.contract_id
)
 cpnum,
 (
    SELECT COUNT(*) FROM ext_cproduct_c WHERE contract_product_id IN(
        SELECT contract_product_id WHERE contract_id = c.contract_id
    )
 )
epnum
,c.*
FROM contract_c c

3、查询合同(contract_c)对应的货物(contract_product_c)的数量(cnumber)和价格（price）的乘积


SELECT  
    c.contract_no,
    cp.cpnum,
    cp.cptotal
FROM
(
SELECT * FROM contract_c
)c
LEFT JOIN
(
SELECT contract_id,COUNT(*) AS cpnum,SUM(cnumber*price) AS cptotal FROM contract_product_c
GROUP BY contract_id
)cp
ON c.contract_id = cp.contract_id

4、

SELECT c.contract_no,
    t.factory_name,t.product_no,
    t.ext_factory_name,t.ext_product_no
    FROM
(SELECT contract_id,contract_no FROM contract_c)c
LEFT JOIN
    (SELECT cp.contract_id,
        cp.factory_name,cp.product_no,
        ep.factory_name AS ext_factory_name,ep