Константин Цветков

Не все каскадные удаления разрешены.

Если каскадные удаления не были определены во время проектирования, придётся определять спецификацию обновления и удаления каждой связи, которая мешает удалению.

Кроме удаления по связи может быть обнуление (NULL) или установка значения по-умолчанию. Это может быть необходимо по физической модели.

Удаление "грязных" данных лучше не делать автоматически, так как могут пострадать "чистые". Можно удалять строки в таблицах по связям (JOIN) отдельно.

Это называется PIVOT: hp+vertica+pivot.

Например, календарный отчёт, на MS SQL.

таблица с полями: id, month_id, kpi1, kpi2
чтобы стало: id, kpi1-2020-01-01, kpi1-2020-02-01, kpi2-2020-01-01, kpi2-2020-02-01.

Без PIVOT

SELECT ID, SUM(M1K1), SUM(M2K1), SUM(M1K2), SUM(M2K2) FROM
(SELECT ID, kpi1 AS M1K1, 0 AS M2K1, kpi2 AS M1K2, 0 AS M2K2 FROM Table_K WHERE month_id = 1
UNION
SELECT ID, 0, kpi1, 0, kpi2  FROM Table_K WHERE month_id = 2) AS DailyData
GROUP BY ID

SELECT Users.UserID, Users.ManagerId
FROM Users 
    INNER JOIN Wallets ON Users.UserID = Wallets.UserID 
    INNER JOIN WalletSections ON Wallets.WalletID = WalletSections.WalletID 
    INNER JOIN Transactions ON WalletSections.WalletSectionID = Transactions.WalletSectionID
  GROUP BY Users.UserID, Users.ManagerId
  HAVING (MAX(Transactions.CreationDate) BETWEEN '2018-10-01 00:00:00' AND '2018-10-31 23:59:59')

и в деталях данной транзакции прописано значение Users.ManagerId.

SELECT Users.UserID
FROM Users 
    INNER JOIN Wallets ON Users.UserID = Wallets.UserID 
    INNER JOIN WalletSections ON Wallets.WalletID = WalletSections.WalletID 
    INNER JOIN Transactions ON WalletSections.WalletSectionID = Transactions.WalletSectionID
  WHERE (Transactions.Detail = Users.ManagerId)
  GROUP BY Users.UserID
  HAVING (MAX(Transactions.CreationDate) BETWEEN '2018-10-01 00:00:00' AND '2018-10-31 23:59:59')

SELECT Users.ManagerId, SUM(WalletSections.Balance)
  FROM Users
    INNER JOIN Wallets ON Users.UserID = Wallets.UserID
    INNER JOIN WalletSections ON Wallets.WalletID = WalletSections.WalletID
  WHERE (WalletSections.Currency = 2)
  GROUP BY Users.ManagerId
  HAVING SUM(WalletSections.Balance) > 10000

все записи из таблицы companies

SELECT companies.name, MAX(polls.created_at)
  FROM polls 
    INNER JOIN teams ON polls.team_id = teams.id
    RIGHT OUTER JOIN companies ON teams.company_id = companies.id
  GROUP BY companies.Name

SELECT Users.UserID
  FROM Users 
    INNER JOIN Wallets ON Users.UserID = Wallets.UserID 
    INNER JOIN WalletSections ON Wallets.WalletID = WalletSections.WalletID 
    INNER JOIN Transactions ON WalletSections.WalletSectionID = Transactions.WalletSectionID
  WHERE (WalletSections.Currency = 1) 
    AND (Transactions.CreationDate BETWEEN Users.RegistrationDate AND DATEADD(yy, 1, Users.RegistrationDate))
  GROUP BY Users.UserID
  HAVING (COUNT(*) > 30)

Но в таблице users нет поля Phone, оно в таблице contact_info

SELECT * FROM contact_info 
  INNER JOIN contact_info AS UserPhone ON contact_info.user_id = UserPhone.user_id
  WHERE UserPhone.Phone  = '1234567'

А если нужен и users

SELECT users.*, contact_info.* FROM contact_info 
  INNER JOIN contact_info AS UserPhone ON contact_info.user_id = UserPhone.user_id
  INNER JOIN users ON users.user_id = UserPhone.user_id
  WHERE UserPhone.Phone  = '1234567'

Если сделаете структуру:

===========================
User_id | InfoType | Value |
---------------------------
1 | 'Email' | 'q@c.com' |
1 | 'Phone' | '1234567' |
===========================

SELECT users.*, contact_info.* FROM contact_info 
  INNER JOIN contact_info AS UserPhone ON contact_info.user_id = UserPhone.user_id
  INNER JOIN users ON users.user_id = UserPhone.user_id
  WHERE UserPhone.InfoType = 'Phone' AND UserPhone.Phone  = '1234567'

SELECT reg_num, MAX( date_creation ) FROM YourTable GROUP BY reg_num

id этого объекта получить?

SELECT id, YourTable.reg_num, YourTable.date_creation 
  FROM YourTable INNER JOIN
    ( SELECT reg_num, MAX( date_creation ) AS MaxDate FROM YourTable GROUP BY reg_num ) AS M
      ON M.reg_num = YourTable.reg_num AND M.MaxDate = YourTable.date_creation

dic_value VARCHAR(MAX)

Вы не сможете сделать ограничение уникальности на это поле. Создайте вычисляемое поле, например, хеш и ограничивайте его.

CREATE TABLE [dbo].[Dic](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Value] [varchar](max) NOT NULL,
	[ValueUnique]  AS (hashbytes('MD5',[Value])),
 CONSTRAINT [PK_Dic] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, 
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE UNIQUE NONCLUSTERED INDEX [IX_Dic] ON [dbo].[Dic]
(
	[ValueUnique] 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 admin, COUNT( DISTINCT user ) 
  FROM table
  GROUP BY admin

SELECT * FROM storehouses_products
  ORDER BY CASE WHEN value = 0 THEN 2147483647 ELSE value END

Или ваш вариант:

SELECT 0 AS line, * FROM storehouses_products WHERE value > 0
UNION 
SELECT 1 AS line, * FROM storehouses_products WHERE value < 1
ORDER BY line, value

Второй слева.

SELECT `experience`.`role` AS role, COUNT(*) AS count_users
FROM `experience`
GROUP BY `role`
UNION
SELECT 'Not role', 
  ( SELECT COUNT(*) FROM `users` ) - ( SELECT COUNT( DISTINCT( `user_id` )) FROM `experience` )
ORDER BY 2 DESC

ROWTERMINATOR = '0x0A'