总结一下昨天写sql时新接触到的知识点。
SELECT
c.`name` AS '客户姓名',
c.customer_adderss AS '客户地址',
c.phone AS '客户联系电话',
c.serial AS '客户编码',
o.material_name AS '物料名称',
o.material_serial AS '物料编码',
o.material_single_price AS '物料采购单价',
prostv2.`value` AS '材质',
CASE o.material_unit
WHEN 'double' THEN
'双'
WHEN 'dozen' THEN
'打'
WHEN 'Pcs' THEN
'Pcs'
......
END AS '单位',
o.material_spec AS '规格型号',
prostv.`value` AS '颜色',
proc.`name` AS '品类',
pros.`name` AS '系列',
o.material_num AS '数量',
CASE pu.type
WHEN '01' THEN
'仓库备货'
WHEN '02' THEN
'直运客户'
END AS '采购类型',
s.`name` AS '供应商名称',
pu.create_date AS '采购订单创建日期'
FROM
order_details AS o
LEFT JOIN customer AS c ON c.id = o.customer_rid
LEFT JOIN product AS p ON p.id = o.product_rid
LEFT JOIN product_series AS pros ON pros.id = p.product_series_id
LEFT JOIN product_category AS proc ON proc.id = pros.product_category_id
LEFT JOIN product_series_type_value AS prostv ON prostv.id = p.colour_type_id
LEFT JOIN product_series_type_value AS prostv2 ON prostv2.id = p.material_type_id,
purchase_order AS pu
LEFT JOIN supplier AS s ON s.id = pu.supplier_id
WHERE
pu.`status` != '0'
AND o.purchase_order_id = pu.id
AND o.`status` != '0'
AND pu.create_date BETWEEN '2019-01-01 00:00:00'
AND '2019-05-01 00:00:00'
ORDER BY
pu.create_date DESC;
case when
首先是遇到了为查询出来的值设置别名的情况,因为数据库存的值和展示给用户看的值是不一样的,懂的都懂。这种情况需要使用 case when语句。
简单总结一下就是:
以case
开头,然后针对对应的字段匹配不同的值,这些情况写在 when ... then ...
块中,最后以end
结尾,并通过as
设置该列的别名。
left join
以前基本没有用过左连,右连,并不清楚和直接使用where有什么区别,直到写这个sql的时候才发现两者得到的数据结果可能是不同的。可以看一下对比分析。
这里我比较直观的感受是,当用一切条件都用where筛选时,可能会比你预期的数据量少,因为有些条件限制会导致一部分不符合的数据丢失;但是使用left join时,如果你能够将表正确拼接,就不会出现数据丢失的情况,所以我上面使用了很多left join,实际上拼接出了一张很大的表,将属性值横向扩展了很多,但是可以保证在where限制条件下数据量正确无误。如果把所有条件都放到where中,就会出现一部分数据丢失,比如有一些order_details
的productRid
为空,如果where中出现o.product_rid = p.id
时,就会过滤掉这些为空的数据,导致数据量变少。
关于datetime类型的比较
sql中对datetime类型的数据进行区间范围比较时,不要用字符串的形式比较,比如将
pu.create_date BETWEEN '2019-01-01 00:00:00' AND '2019-05-01 00:00:00'
写成
pu.create_date > '2019-01-01 00:00:00' AND pu.create_date < '2019-05-01 00:00:00'
尽量不要使用字符串的形式比较,比较方法可以参考这篇文章。