工作常用到的sql语句

1.Sql server 2005 提供了一个新的函数Row_Number()来产生行号，可以用来删除重复数据，分页数据

 产生行号      

select row_number() over(order by name asc) as RowId,*from student

RowId      name      address

1            ike          beijing
2           ike2         shanghai
3           ike3         suzhou
4           ike3         suzhou
删除重复的数据

With[sqlRowNumber]as
(select row_number() over(order by name asc) as RowId,* from Student)
delete from [sqlRowNumber] where RowId=3

 

2.选出重复的数据

select name,address from student group by name,address having count(*)>1

 

3.复制表的数据，表的结构需要一样

insert into Tstudent select * from student

 

4.递归
列出父级的所有子级
例如:公司部门的数据
table:Department
SubDepartment         ParentDepartment
       A1                               A
      A21                             A1
      A31                            A21

With [RootNode] as
(
  SELECT SubDepartment,ParentDepartment FROM  department WHERE ParentDepartment='A'
  UNION ALL
  SELECT department.SubDepartment,department.ParentDepartment FROM RootNode INNER JOIN
 department ON RootNode.SubDepartment=department.ParentDepartment

)
SELECT *FROM RootNode

5.

select  TabReportChange_date,SUM(TabReportChange_subtotal),SUM(TabReportChange_discount),
SUM(TabReportChange_tax),SUM(TabReportChange_total),
CASE
WHEN TabReportChange.TabReportChange_date = '2015-06-01' THEN '0Mon'
WHEN TabReportChange.TabReportChange_date = '2015-06-02' THEN '1Tue'
WHEN TabReportChange.TabReportChange_date = '2015-06-03' THEN '2Wed'
WHEN TabReportChange.TabReportChange_date = '2015-06-04' THEN '3Thu'
WHEN TabReportChange.TabReportChange_date = '2015-06-05' THEN '4Fri'
WHEN TabReportChange.TabReportChange_date = '2015-06-06' THEN '5Sat'
WHEN TabReportChange.TabReportChange_date = '2015-06-07' THEN '6Sun'
ELSE NULL END AS 'C',count(*) AS count
from TabReportChange
left JOIN (select TabReport_num,TabReport_status from TabReport where TabReport_status = 0  GROUP BY TabReport_status) T2 ON T2.[TabReport_num] = [TabReportChange].[TabReportChange_num]
where (TabReportChange.TabReportChange_date >= '2015-06-01'  AND TabReportChange.TabReportChange_date <= '2015-06-07')  and T2.TabReport_status=0

 6. insert into 值中某一个从数据表取一个字段值

insert into TabTax(company_num,TabTax_num,TabTax_name,TabTax_value) select '1000',max(tabtax_num)+1,'aaa','9.6' from tabtax where company_num='1000'

 

INSERT INTO file_detail(file_id,category_id,create_time)SELECT id,
(SELECT id FROM file_category WHERE category_text='CCCC'),'2021/01/08 15:35:20' FROM file_list WHERE file_path='D:/Project/Qt/1.txt'

 

 

 

7.sqlite 日期时间函数

 select date(A.add_time),time(A.add_time),A.pos_no,B.product_name,A.weight,A.sale_price,A.sale_total,A.add_user from tab_sale A left join tab_product B on A.product_num=B.product_num  where  A.add_time>='2016-02-18 00:00' and A.add_time<='2016-02-18 23:29'

 

8.组合查询

红色语句是子查询

select A.product_num,B.product_name,sum(A.sale_total),C.customers,count(A.product_num),sum(A.weight) from tab_sale A left join tab_product B on A.product_num=B.product_num
left join (select product_num,count(product_num)as customers from (select product_num,list_num from tab_sale  group by list_num,product_num) group by product_num) C on A.product_num=C.product_num
 group by A.product_num