UPDATE Admins A
JOIN Users U ON U.UserID = A.AdminID
SET A.password = U.password
WHERE U.UserID = 123;UPDATE Admins
SET Admins.password = U.password
FROM Admins A JOIN Users U ON U.UserID = A.AdminID
WHERE A.AdminID = 123;SELECT shop.id, shop.name, SUM(sales.amount) sales_amount
FROM sales
JOIN shops ON sales.shop_id = shops.id
GROUP BY sales.shop_id, shop.id, shop.nameSELECT
Site_id, url,
COUNT(DISTINCT site_area.site_area_id) active_blocks,
SUM(impression_count) impression_count,
SUM(revenue) revenue
FROM site
JOIN site_area USING(site_id)
LEFT JOIN npm_site_area_stat_cache USING(site_area_id)
WHERE site_area.status = 1
GROUP BY site_id, url
HAVING COUNT(DISTINCT site_area.site_area_id) > 3;SELECT
pn.country, array_agg(s.name) services
FROM number pn
CROSS JOIN service s
LEFT JOIN buy_number bn ON bn.numberId = pn.id AND bn.serviceId = s.id
WHERE bn.id is NULL
GROUP BY pn.country;SELECT
`authors`.`id`, `authors`.`last_name`, `authors`.`first_name`, COUNT(*) `author_count`
FROM `bookissue`
JOIN `books` ON `books`.`id` = `bookissue`.`idBook`
JOIN `authors` ON `authors`.`id` = `books`.`idAuthor`
WHERE YEAR(`bookissue`.`data`) = ?
GROUP BY `authors`.`id`, `authors`.`last_name`, `authors`.`first_name`
ORDER BY `author_count` DESC
LIMIT 1<?php
function saveEmail($contactId, $email){
global $pdo;
try {
$stmt = $pdo->prepare("
INSERT INTO emails(contact_id, email)
VALUES (:contact_id, :email)");
$stmt-> bindValue(':contact_id', $contactId);
$stmt-> bindValue(':email', $email);
return $stmt-> execute();
} catch (PDOException $Exception ) {
print_r($Exception);
return false;
}
}
saveEmail(166, 'no@mail.com');<?php
$query = "select * from equipment";
$stmt = $pdo->prepare($query);
$stmt->execute();
$equipments = $stmt->fetchAll(PDO::FETCH_ASSOC);
$result = array_reduce(
$equipments,
function($res, $el) {
if(!is_array($res[$el["equipment_id"]])) {
$res[$el["equipment_id"]] = [
"equipment_id" => 141491,
"speed" => []
];
}
array_push(
$res[$el["equipment_id"]]["speed"],
[
"datetime" => $el["datetime"],
"value" => $el["speed"],
"distance" => $el["distance"],
]
);
return $res;
},
[]
);
var_export(array_values($result));<?php
$query = "select
equipment_id,
json_arrayagg(
json_object(
'datetime', `datetime`,
'value', `speed`,
'distance', `distance`
)
) speed
from equipment
group by equipment_id;";
$stmt = $pdo->prepare($query);
$stmt->execute();
$equipments = $stmt->fetchAll(PDO::FETCH_ASSOC);
var_export($equipments);Обратите внимание, что если у вас есть составной индекс для (c1, c2, c3), у вас будут возможности индексированного поиска для одной из следующих комбинаций столбцов:
SELECT *
FROM table_name
WHERE c1 = v1;
SELECT *
FROM table_name
WHERE c1 = v1 AND c2 = v2;
SELECT *
FROM table_name
WHERE c1 = v1 AND c2 = v2 AND c3 = v3;<?php
$text = 'Это первая строка текста.
А это вторая строка.
А здесь третья строка.';
// split to rows
$rows=explode(PHP_EOL, $text);
//get first 2 rows
$first2rows = array_slice($rows, 0, 2);
//implode to new text
$newtext = implode(PHP_EOL,$first2rows);
var_export($newtext);<?php
$operator = "Borat";
// Check if operator exists
$stmt = $pdo->prepare(
"SELECT id_operator FROM operators WHERE name_operator =:name_operator;"
);
$stmt->execute(["name_operator" => $operator]);
$result = $stmt->fetch(PDO::FETCH_ASSOC);
// If not exists store new record
if (!$result) {
$sth = $pdo->prepare(
"INSERT INTO operators (name_operator) VALUES (:name_operator);"
);
$sth->execute(["name_operator" => $operator]);
$id_operator = $pdo->lastInsertId();
} else {
$id_operator = $result["id_operator"];
}
echo "id_operator: $id_operator";CREATE TABLE orders (
price DOUBLE,
amount DOUBLE,
total DOUBLE AS (price * amount)
);CREATE VIEW union_tables
AS
SELECT id, name FROM a
UNION ALL
SELECT id, name FROM b;select
sum(if(`type` = 0, `sum`, 0)) `income`,
sum(if(`type` = 1, `sum`, 0)) `outcome`,
sum(if(`type` = 0, `sum`, -`sum`)) `balance`
from tablename;SELECT
orugie, sum_cena_orugie, pos, sum_cena_pos
FROM (
SELECT row_number() over (order by id_orugie_p) orugie_rn, id_orugie_p orugie, SUM(cena) sum_cena_orugie
FROM test
GROUP BY id_orugie_p
) t_orugie
FULL JOIN (
SELECT row_number() over (order by id_pos_p) pos_rn, id_pos_p pos, SUM(cena) sum_cena_pos
FROM test
GROUP BY id_pos_p
) t_pos on orugie_rn = pos_rn;