一.如何进行列转换
cross join
eg:Select * from (
select sum(kills) as '沙僧' from user1 a join user_kills b on a.id = b.user_id and a.'user_name'='沙僧' ) a cross join (
select sum(kills) as '猪八戒' from user1 a join user_kills b on a.id = b.user_id and a.'user_name'='猪八戒 ' ) b cross join (
select sum(kills) as '孙悟空' from user1 a join user_kills b on a.id = b.user_id and a.'user_name'='孙悟空 ' ) c
二.从数据表晨随机取N条数据
mysql: select * from tablename order by rand() limit 10
mqlserver: select top 10 * from tablename order by NEWID()
三.SQL查找出一个字段重复的值（每个重复的值取出一个）
select * from dealer a where id=(select min(id) from dealer where province_id=a.province_id)
四.SQL not in 优化
select * fromipcstorage.cachetmpaleft join (select lblid from ipcconfigdb.labelfilter where ftype=2 )bon a.lblid = b.lblid whereb.lblid is null
5.sql 日期转换
FROM_UNIXTIME()
FROM_UNIXTIME(a.activity_start_time, \'%Y-%c-%d %h:%i:%s\' ) as activity_start_time,FROM_UNIXTIME(a.activity_end_time, \'%Y-%c-%d %h:%i:%s\' ) as activity_end_time
6.sql CASE WHEN
UPDATE `juo_card_group` SET `is_package` = (CASE WHEN `is_package` = '1' THEN '1,2' ELSE '1' END);
7.sql 删除重复的数据https://www.cnblogs.com/wangfuyou/p/6058169.html
DELETE
FROM
ips_activity_product
WHERE
id IN (
SELECT
b.id
FROM
(
SELECT
a.id
FROM
ips_activity_product a
WHERE
(a.activity_child_id, a.sku) IN (
SELECT
activity_child_id,
sku
FROM
ips_activity_product
GROUP BY
activity_child_id,
sku
HAVING
count(*) > 1
)
AND id NOT IN (
SELECT
min(id)
FROM
ips_activity_product
GROUP BY
activity_child_id,
sku
HAVING
count(*) > 1
)
) b
)
网友评论