You may optionally enter a comparison operator (<, <=, >, >=, <> or =) at the beginning of each of your search values to specify how the comparison should be done.
WITH a AS (
SELECT 1 AS n UNION ALL
SELECT 2 AS n UNION ALL
SELECT 3 AS n UNION ALL
SELECT 4 AS n
),
b AS (
SELECT 4 AS n UNION ALL
SELECT 5 AS n UNION ALL
SELECT 6 AS n UNION ALL
SELECT 7 AS n
)
SELECT * FROM a
EXCEPT -- <------------------------------
SELECT * FROM b
298
EXASOL
Datum, Zeit
Aktuelles Datum/Uhrzeit
SELECT TO_CHAR(CURRENT_TIMESTAMP, 'YYYY-MM-DDTHH24:MI:SS.FF3') AS iso_date_string
293
BigQuery
Datum, Zeit
Gestriges Datum
SELECT
CURRENT_TIMESTAMP() AS today_timestamp
, DATE_ADD(CURRENT_DATE(), INTERVAL -1 DAY) AS yesterday_as_date
294
BigQuery
Datenmengen
Differenzen anzeigen
WITH a AS (
SELECT * FROM UNNEST([1,2,3,4]) AS n
),
b AS (
SELECT * FROM UNNEST([4,5,6,7]) AS n
)
SELECT * FROM a
EXCEPT DISTINCT -- <------------------
SELECT * FROM b
SELECT FORMAT_TIMESTAMP("%FT%H:%M:%E3S", CURRENT_DATETIME()) AS iso_date_string
300
BigQuery
Datenbank
UPDATE SQL mit JOIN
UPDATE
`project.dataset.table1` t1
SET
t1.target_column = t2.source_column
FROM
`project.dataset.table2` t2
WHERE
t1.join_key = t2.join_key;
283
Yii2
Datenbank
Verzeichnis erstellen
<?php
// Example as SQL:
// SELECT * FROM MainTable
// WHERE fieldA=5 AND fieldB IN (SELECT id FROM SubTable WHERE fieldC=551)
$fieldC = 551;
$SubTable_idQry = SubTable::find()->select('id')->where(['fieldC' => $fieldC]);
// Static way:
$fieldA = 5;
// As result:
$fieldA = ThirdTable::find()
->select('fieldA_Reference')
->where(['fieldC' => $fieldC])
->one();
$MainTableResultQry = MainTable::find()
->where(['in', 'fieldB', $SubTable_idQry])
->andWhere(['fieldA' => $fieldA])
->all();
?>
284
Yii2
Datenbank
Abfrage mit Aggregration
<?php
/* // Example as SQL
SELECT
FieldA
,COUNT(FieldA) as cnt
FROM MainTable
WHERE FieldA = 5
GROUP BY FieldA
*/
$MainTableResultQry = MainTable::find()
->select(['FieldA', 'COUNT(FieldA) as cnt'])
->where(['FieldA' => 5])
->groupBy(['FieldA'])
->createCommand()
->queryAll();
?>
286
Yii2
Yii2-ActiveQuery
Abfrage mit Subselect
<?php
/*
SQL Example:
SELECT * FROM `Cities`
WHERE
(`postalcode_id` IN (SELECT `id` FROM `postalcodes` WHERE `postalcode`='12345')
)
OR
(`region_id` IN (SELECT `id` FROM `regions` WHERE `region`='North')
)
*/
$active_query_postalcodes_For_Subselect = \app\models\Postalcodes::find()->select("id")->where(["postalcodes" => '12345']); // Attention! No "->all()" at the end to create a subselect!
$active_query_regions_For_Subselect = \app\models\Regions::find()->select("id")->where(["region" => 'North']); // Attention! No "->all()" at the end to create a subselect!
$model = new \app\models\Cities();
$all_Cities_With_IN_and_OR = $model::find()
->where(['in', "postalcode_id", $active_query_postalcodes_For_Subselect])
->orWhere(['in', "region_id", $active_query_regions_For_Subselect])
->all();
?>
