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
;
SELECT
name,
ROUND(AVG(establishment_avg_rate), 2) avg_rate
FROM (
SELECT
chains.name,
ROUND(AVG(rate), 2) establishment_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, Establishment.name
) establishment_avg
GROUP BY name
ORDER BY avg_rate DESC;
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();
}
create table comments (
"name" varchar,
"commens" varchar,
"like" int,
"date" timestamp
);
insert into comments
select (json_populate_record(null::comments, value)).* from json_array_elements('[
{
"name": "name1",
"commens": "commens1",
"like": 2,
"date": "2022-01-01"
},{
"name": "name2",
"commens": "commens2",
"like": 2,
"date": "2022-01-01"
},{
"name": "name3",
"commens": "commens3",
"like": 2,
"date": "2022-01-01"
}
]');
select * from comments;
$sql = "CREATE TABLE таблица1(
id INTEGER PRIMARY KEY,
col1 INT,
col2 TEXT
);";
$db->exec($sql);
$sql = "INSERT INTO таблица1(col1, col2) VALUES
('aaaa', 'bbbbb'),
('xxx', 'yyy');";
$db->exec($sql);
$sql = "SELECT * FROM таблица1;";
$result = $db->query($sql);
while ($row = $result->fetchArray(SQLITE3_ASSOC)) {
$array[] = $row;
}
print json_encode($array);
<?php
$c = 5;
$min_price = 20;
$c = max($c, $min_price);
printf("Цена: %s руб.", $c);
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();
}
}
$citys = mysqli_query($connect, "SELECT * FROM `city`");
$citys = mysqli_fetch_all($citys);
echo '<select name="magaz">' . PHP_EOL;
foreach ($citys as $city) {
echo '<option value=' . $city[0] . '>' . $city[1] . '</option>' . PHP_EOL;
}
echo '</select>' . PHP_EOL;
select *
from table1
left join table2 on table1.code = table2.id1
where table1.code = 1;
SELECT *
FROM users
WHERE name = 'Мария' AND city = 'Саратов';
SELECT *
FROM users
WHERE name IN ('Саратов', 'Мария') AND city IN ('Саратов', 'Мария');
-- Используя 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;
$query = "SELECT * FROM `mytable` ";
$result = $mysqli->query($query);
$rows = $result->fetch_all(MYSQLI_ASSOC);
$MyListArray = [];
while ($row = $queryResult->fetch_assoc()) {
$MyListArray[] = $row;
};