+=========+=======+==========+============+============+============+============+
| fam | name | otch | 2021-03-17 | 2021-03-18 | 2021-03-19 | 2021-03-23 |
+=========+=======+==========+============+============+============+============+
| Петров | Иван | Иванович | 0 | 0 | 3 | 3 |
+---------+-------+----------+------------+------------+------------+------------+
| Сидоров | Петр | Иванович | 4 | 4 | 0 | 4 |
+---------+-------+----------+------------+------------+------------+------------+
| Смирнов | Федор | Иванович | 0 | 0 | 0 | 5 |
+---------+-------+----------+------------+------------+------------+------------+CREATE TABLE Отпуск
(Кодотпуска INTEGER PRIMARY KEY,
ТипОтпуска CHAR (100) NOT NULL,
ОплатаОтпуска FLOAT CHECK (ОплатаОтпуска > 10000),
ЛьготыПоОтпуску FLOAT NOT NULL);+==================+======+===========+
| name | code | AutorCode |
+==================+======+===========+
| BookWithAutor | 1 | 1 |
+------------------+------+-----------+
| BookWithoutAutor | 3 | Empty |
+------------------+------+-----------+Select ItemId as ItemId,
[fieldid1], [fieldid2], [fieldid3], [fieldid4], [fieldid5]
From
(Select
items.id as ItemId,
Coalesce(itemfields.en,itemfields.ru,itemfields.ko) as FieldName,
itemfields.fieldid as fieldid,
fields.key as value
From
from items as items
left join item_fields as item_fields on items.id = item_fields.item_id
left join fields as fields On item_fields.fieldid = fields.id) SourceTable
pivot
(
Max(value) for fieldid in ([fieldid1], [fieldid2], [fieldid3], [fieldid4], [fieldid5] )
) as PivotTable;SELECT
t1.id, COUNT(t2.field2) AS cnt, SUM(t2.field2) AS sum_field2
FROM tbl1 as t1
left join tbl2 as t2
ON t1.id = t2.id
group by t1.idSelect
CommentTable.user_id,
CommentTable.comment,
CommentTable.created_ts
From
comments as CommentTable
Inner join
(Select
t.user_id as user_id ,
Max(t.created_ts) as LastTime
From comments as t
Group by t.user_id) as LastTimeTable
On CommentTable.user_id = LastTimeTable.user_id
and CommentTable.created_ts = LastTimeTable.LastTimeCreate table #tt1
(user_id nvarchar(10),
user_name nvarchar(100))
Insert into #tt1
Select user_id , user_name from usersCreate table #tt1
(user_id nvarchar(10),
user_name nvarchar(100))
Insert into #tt1
Select user_id , user_name from users
Select
CommentTable.user_id,
users.user_name,
CommentTable.comment,
CommentTable.created_ts
From
comments as CommentTable
Inner join
(Select
t.user_id as user_id ,
Max(t.created_ts) as LastTime
From comments as t
Group by t.user_id) as LastTimeTable
On CommentTable.user_id = LastTimeTable.user_id
and CommentTable.created_ts = LastTimeTable.LastTime
Left join #tt1 as users
on CommentTable.user_id = users.user_id
drop table #tt1-- Создаем временную таблицу для реализации примера
Create table #tt1
(_Code nvarchar(10),
_Name nvarchar(100))
-- Наполняем временную таблицу тестовыми данными
Insert into #tt1
Select '1.', 'Муромец'
Insert into #tt1
Select '2.', 'Никитич'
Insert into #tt1
Select '2.', 'Муромец'
Insert into #tt1
Select '3.', 'Плясовая'
Insert into #tt1
Select '3.', 'Никитич'
-- Выбираем строки где поле _Code повторяется 2 и более раз
Select
t1._Code,
t1._Name,
t3.Counter
From
#tt1 as t1
inner join(
Select
t2._Code,
COUNT(t2._Name) as counter
from #tt1 as t2
group by
t2._Code
Having COUNT(t2._Name) >1
) as t3
on t1._Code = t3._Code
-- Удаляем временную таблицу.
drop table #tt1