简单sql查询

最新推荐文章于 2020-06-09 16:52:57 发布

暗夜螃蟹

最新推荐文章于 2020-06-09 16:52:57 发布

阅读量643

点赞数

分类专栏： SQL

本文链接：https://blog.csdn.net/thl331860203/article/details/25200629

版权

SQL 专栏收录该内容

19 篇文章 0 订阅

订阅专栏

A表对A表本身做关联处理。

select
ROW_NUMBER() over(partition by DPP_REGID order by CREATEDATE) odb1,
ROW_NUMBER() over(partition by DPP_REGID order by CREATEDATE desc) odb2,
t1.*
into #temp_01
from TEST_POINT t1
where exists
(
SELECT 1
FROM TEST_POINT t2
where t2.DPP_REGID = t1.DPP_REGID
group by t2.DPP_REGID
having COUNT(distinct t2.DPP_ISUSED) > 1
)

主要用到EXISTS关键字做处理。

--新建非聚集索引
CREATE NONCLUSTERED INDEX index_0420_0507 ON tmp_zjuser_0420_0507
(
DPO_DRE_ID ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF,
IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

--多字段统计

SELECT COUNT(CASE
WHEN LEA.LEA_PUSH_STATUS='N' THEN 'N' END )
salesNum,
COUNT(CASE
WHEN DETAIL.LDD_COR_STATUS='Leads_Externally_Qualified' and LEA.LEA_PUSH_STATUS='Y' THEN 'Qualified' END) hotNum,
COUNT(CASE
WHEN DETAIL.LDD_COR_STATUS='Leads_OB_Qualify' and LEA.LEA_PUSH_STATUS='Y' THEN 'OB' END) obNum
FROM LEADS LEA,LOAD_DATA_DETAIL DETAIL
WHERE
LEA.LEA_LDD_ID = DETAIL.LDD_ID
AND LEA.LEA_LDB_ID=3009

----------------------------------------MYSQL-------------------------------------------------------

//查询当天数据
SELECT * FROM t_topic   WHERE CURDATE() = DATE(publishTime);
//查询以周内数据
SELECT * FROM t_topic   WHERE DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= DATE(publishTime);
//查询一个月内数据
SELECT * FROM t_topic   WHERE SELECT DATE_SUB(CURDATE(), INTERVAL 1 MONTH) <= DATE(publishTime);


//补全日期关联

SET @i :=-1;
SELECT
x.`time`
FROM
(
SELECT
	DATE_FORMAT(DATE_SUB(NOW(), INTERVAL(@i:=@i + 1)DAY),'%Y-%m-%d') AS `time`
FROM
(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6) x1, (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6) x2
WHERE
@i < 6
) X

ORDER BY x.time DESC