Etiket arşivi: sql sorguları

SQL’de kullanışlı sorgular

Belirli bir kolon adını bütün veri tabanındaki tablolarda aramak için:

(find column name in database tables)


SELECT c.name AS 'Kolon'
,t.name AS 'Tablo'
FROM sys.columns c
JOIN sys.tables t ON c.object_id = t.object_id
WHERE c.name LIKE '%ARANACAK%'
ORDER BY t.name
,c.name;

Tablo ve Viewlerde aramak için:

(find column name in database tables and views)

SELECT COLUMN_NAME AS 'Kolon'
,TABLE_NAME AS 'Tablo'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE '%ARANACAK%'
ORDER BY Tablo
,Kolon;

Tekrar eden satırları silmek

(remove duplicate rows)

DEMO


CREATE TABLE Table1([col1] varchar(5), [col2] int, [col3] int, [col4] int, [col5] int, [col6] int, [col7] int);

INSERT INTO Table1
([col1], [col2], [col3], [col4], [col5], [col6], [col7])
VALUES
('john', 1, 1, 1, 1, 1, 1),
('john', 1, 1, 1, 1, 1, 1),
('sally', 2, 2, 2, 2, 2, 2),
('sally', 2, 2, 2, 2, 2, 2),
('sally', 2, 2, 2, 3, 2, 2)
;

SELECT * FROM dbo.Table1

;WITH CTE AS(
SELECT [col1], [col2], [col3], [col4], [col5], [col6], [col7],
RN = ROW_NUMBER()OVER(PARTITION BY col1 , col2 , col3 , col4 , col5 , col6 , col7 ORDER BY col1)
FROM dbo.Table1
)
DELETE FROM CTE WHERE RN > 1

SELECT * FROM dbo.Table1

İki tarih arasını ay bazlı olarak listelemek

(get all months and years between two dates)
DEMO


DECLARE 
      @start    DATE = '01.01.2018'
    , @end        DATE = '09.09.2018'

;WITH cte AS 
(
    SELECT dt = DATEADD(DAY, -(DAY(@start) - 1), @start)

    UNION ALL

    SELECT DATEADD(MONTH, 1, dt)
    FROM cte
    WHERE dt < DATEADD(DAY, -(DAY(@end) - 1), @end)
)
SELECT AY = MONTH(DT), YIL = YEAR(DT)
INTO #Dates FROM cte
option (MaxRecursion 0);

SELECT * FROM #Dates

DROP TABLE #Dates