-- Get previous week
SELECT CONCAT(datepart(YEAR, DATEADD(WEEK, -1, GETDATE())) , '-', datepart(wk, DATEADD(WEEK, -1, GETDATE())));
SELECT
SUM(some_value) AS 'Количество order_cost', SUM(just_value) AS 'Количество доставок'
FROM dbo.Sheet1$
WHERE
period = CONCAT(datepart(YEAR, DATEADD(WEEK, -1, GETDATE())) , '-', datepart(wk, DATEADD(WEEK, -1, GETDATE())))for ($i=0; $i<3; $i++) {
$dddt = rand(1,15);
$insert_sql = "INSERT INTO events
SELECT MAX(id) + {$dddt}, NOW(), 10 FROM events;";
mysqli_query($mysqli, $insert_sql);
}select
user_id,
sum(case when param = 'Won' then value else 0 end) 'Won',
sum(case when param = 'Lose' then value else 0 end) 'Lose'
from user_stats
where param in ('Won', 'Lose')
group by user_id;select
orders.*,
DATEDIFF(COALESCE(return_at, CURRENT_DATE), get_at) days_in_rent
from orders;create table teacher(
id int primary key,
name varchar(64)
);
create table student(
teacher_id int,
name varchar(64),
foreign key (teacher_id) references teacher(id)
);
insert into teacher (id, name) values (1, 'Teacher');
insert into student (teacher_id, name) values (1, 'Me');
select *
from student s
join teacher t on t.id = s.teacher_id
;DB::beginTransaction();
try {
foreach ($data as $d) {
DB::table('stage_1')
->where('tournament_id', $d['tournament_id'])
->where('team_id', $d['team_id'])
->where('group_id', $d['group_id'])
->update(['points' => $d['points']]);
}
DB::commit();
} catch (\Exception $e) {
DB::rollback();
}class Stopwatch
{
private $mysqli;
private $stopwatch_id;
private $db;
function __construct($mysqli, $stopwatch_id)
{
$this->db = $_ENV['BD_NAME'];
$this->mysqli = $mysqli;
$this->stopwatch_id = $stopwatch_id;
}
public function start()
{
$timestamp = time();
$query = "
INSERT INTO `$this->db`.`stopwatch` (`chat_id`, `timestamp`)
VALUES (?, ?)
ON DUPLICATE KEY UPDATE timestamp = ?
";
$stmt = $this->mysqli->prepare($query);
/* bind parameters for markers */
$stmt->bind_param("iii", $this->stopwatch_id, $timestamp, $timestamp);
/* execute query */
return $stmt->execute();
}
}-- Используя WHERE NOT EXISTS
SELECT *
FROM QEEN
WHERE NOT EXISTS (
SELECT ID FROM QEEN REVERSE_QEEN WHERE REVERSE_QEEN.A = QEEN.B AND REVERSE_QEEN.B = QEEN.A
);
-- Используя LEFT JOIN
SELECT QEEN.*
FROM QEEN
LEFT JOIN QEEN REVERSE_QEEN ON REVERSE_QEEN.A = QEEN.B AND REVERSE_QEEN.B = QEEN.A
WHERE REVERSE_QEEN.ID IS NULL;
-- Просто извращение, но тоже должно работать :)
WITH ALL_QEEN AS (
SELECT ID, A, B, 1 T FROM QEEN
UNION ALL
SELECT ID, B, A, 2 T FROM QEEN
) SELECT
MIN(ID) ID, A, B
FROM ALL_QEEN
GROUP BY A, B
HAVING COUNT(*) = 1 AND MIN(T) = 1;SELECT * FROM `category`
ORDER BY COALESCE(`orderNum`, `name`)SELECT * FROM `category`
ORDER BY `orderNum` IS NULL, `name`;SELECT concat(
JSON_UNQUOTE(json_extract(additional,'$.surname')),
JSON_UNQUOTE(json_extract(additional,'$.firsname')),
JSON_UNQUOTE(json_extract(additional,'$.lastname'))
) fullname
FROM `user_attributes`
;$query = "
SELECT
items.*,
GROUP_CONCAT(DISTINCT values_color.value) colors,
GROUP_CONCAT(DISTINCT values_base_color.value) base_colors
FROM items
LEFT JOIN values_color ON values_color.item_id = items.id
LEFT JOIN values_base_color ON values_base_color.item_id = items.id
GROUP BY items.id, items.name
;";
// get DB version using PDO
$stmt = $pdo->prepare($query);
$stmt->execute();
$items = $stmt->fetchAll(PDO::FETCH_ASSOC);SELECT type, time_from, time_to
FROM manager_orders
WHERE aptid = 262707
AND cancelled_at is null
AND (
time_from <= '2021-12-07 16:12:00' + INTERVAL 30 Minute AND
time_to >= '2021-12-07 16:12:00' - INTERVAL 60 Minute
);SELECT to_char(
to_timestamp(parameters->>'time', 'HH24:MI') - (parameters->>'offset' || ' seconds')::interval,
'HH24:MI'
)
FROM reports;SELECT AUTHOR
FROM POSSESSION
INNER JOIN BOOKS ON BOOK_ID = BOOKS.ID
WHERE TAKING_DATE > (CURRENT_DATE - INTERVAL '1 year')
GROUP BY AUTHOR
ORDER BY COUNT(AUTHOR) DESC
FETCH FIRST 1 ROWS WITH TIES;SELECT
chains.name,
ROUND(AVG(rate), 2) avg_rate
FROM Establishment
JOIN Review ON Review.establishment_id = Establishment.id
JOIN Rate ON Rate.review_id = Review.id
JOIN Establishment chains ON chains.id = COALESCE(Establishment.chain_id, Establishment.id)
GROUP BY chains.name
ORDER BY avg_rate DESC
;select
office_id,
json_arrayagg(employee_id) employee_ids
from t
group by office_id;+===========+==============+
| office_id | employee_ids |
+===========+==============+
| 1 | [1, 2] |
+-----------+--------------+