实用SQL

查询表中包含的字段名

SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = N’Viw_TakedLandInfo2SJ'

表中字段名，数据类型，长度

SELECT 
    c.name 'Column Name',
    t.Name 'Data type',
    c.max_length 'Max Length'
FROM    
    sys.columns c
INNER JOIN 
    sys.types t ON c.user_type_id = t.user_type_id
LEFT OUTER JOIN 
    sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id
LEFT OUTER JOIN 
    sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id
WHERE
    c.object_id = OBJECT_ID('View_PMastData_SupplierBankAccountInfo')
ORDER BY c.max_length,t.Name,c.name

行转列

SELECT 
Projname,design_institute_name,
SUM(CASE param_value_code WHEN 'Acreage' THEN CAST(regist_quota_value AS INT) ELSE 0 END) AS 'Acreage', 
SUM(CASE param_value_code WHEN 'Plot Ratio' THEN CAST(regist_quota_value AS INT) ELSE 0 END) AS 'Plot Ratio', 
SUM(CASE param_value_code WHEN 'Green Ratio' THEN CAST(regist_quota_value AS INT) ELSE 0 END) AS 'Green Ratio', 
SUM(CASE param_value_code WHEN 'Building Density' THEN CAST(regist_quota_value AS INT) ELSE 0 END) AS 'Building Density' 
from
(
SELECT
dcxm.Projname,jdi.design_institute_name,jp.param_value_name,jrq.regist_quota_value,jp.param_value_code
from dcxm 
LEFT JOIN jzy_design_institute jdi on dcxm.mdm_m_Projectid = jdi.project_code 
LEFT JOIN jzy_regist_quota jrq on jrq.design_institute  = jdi.id 
LEFT JOIN jzy_parameter jp on jrq.regist_quota_code = jp.param_value_code 
WHERE jp.param_value_code in ('Acreage','Plot Ratio','Green Ratio','Building Density') 
) p 
Group by Projname,design_institute_name 
ORDER BY p.Projname,p.design_institute_name 

select count(*) from (
select a.sales_system_code from sales_floor as a
where NOT EXISTS (
select b.sales_system_code from sales_project as b where a.project_id = b.project_id
)) A where A.sales_system_code IS NOT NULL

select COLUMN_NAME,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH from information_schema.columns
where table_name = 'PMD_Floor'
AND COLUMN_NAME like 'SellSystemCode'

数据转换一类

convert(date,’2018-08-22 ’)
convert(datetime,’2018-08-22 ’)

开窗函数

SQL底层的实现

底层的实现逻辑是什么
Spark SQL源码分析之核心流程 - CSDN博客

select的内核实现原理 - CSDN博客

select 的内核实现原理，知道怎么用的，还要知道为什么会是这个样子

SQL的出处

SQL 是一门 ANSI（American National Standards Institute 美国国家标准化组织）标准的计算机语言，但是仍然存在着多种不同版本的 SQL 语言。

INSERT INTO table_name
VALUES (value1,value2,value3,...);

为什么是上面那样，而不是下面这样
INSERT table_name
VALUES (value1,value2,value3,...);

UPDATE table_name
SET column1=value1,column2=value2,...
WHERE some_column=some_value;

而不是下面这样
UPDATE into table_name
SET column1=value1,column2=value2,...
WHERE some_column=some_value;

DELETE FROM table_name
WHERE some_column=some_value;
而不是
DELETE table_name
WHERE some_column=some_value;

SQL函数的意义与实现

最大，最小，排序都是怎么实现的

数据类型的转换的源码是什么样子的，

convert(datetime,’2018-08-22’)
convert(datetime,’2018/08/22’)
convert(datetime,’2018:08:22’)
如何识别上面一类字符的区别的

最大

最小