base de datos – ¿Cómo puedo unir dos tablas (T1 y T2) en SQL, que muestre de T1 todos los registros y de T2 solo los registros que tengan alguna relación con T1?

Buenos días estimados colegas, espero se encuentren bien. Actualmente me encuentro realizando una consulta en SQL Server para ver el reporte de ventas mensual de un producto en específico. El detalle está en que, cando intento hacer LEFT JOIN (esto porque quiero ver todos los productos, aunque no tenga ventas registradas) los resultados son similares a INNER JOIN, o sea, solo muestra los datos que tiene relación.

Consulta utilizada:

select ARTICULO, sum(CANTIDAD) –CANTIDAD es el número de unidades vendidas
from ARTICULO LEFT JOIN VIEW_VENTAS_EXACTUS on ARTICULO = ARTCODIGO
where PROVEEDOR = 00024 –Los productos están organizados por proveedor
AND MONTH(FECHA_FACTURA) = 2 –estoy probando con el mes de febrero
AND YEAR(FECHA_FACTURA) = 2021 –del presente año 2021
AND ACTIVO = ‘S’ –solo se muestran los productos que están activos
group by ARTICULO
order by ARTICULO

Resultado:
|ARTICULO |sum(CANTIDAD)|
|00494 |4.00000000 |
|00495 |0.00000000 |
|00499 |20.00000000 |
|00501 |44.00000000 |
|00503 |222.00000000 |
|00504 |781.00000000 |
|00505 |551.00000000 |

Pero necesito que la consulta me arroje algo así:

|ARTICULO |sum(CANTIDAD)|
|00494 |4.00000000 |
|00495 |0.00000000 |
|00496 |NULL |
|00499 |20.00000000 |
|00500 |NULL |
|00501 |44.00000000 |
|00502 |NULL |
|00503 |222.00000000 |
|00504 |781.00000000 |
|00505 |551.00000000 |
|04915 |NULL |
|04916 |NULL |

Gracias de ante mano por la ayuda que me puedan brindar.

sql – Java Spring JPA conversion to myBatis

For homework I was asked to implement myBatis. However, I already started all of my project using Spring Data JPA. And so I need to translate JPA query to myBatis. here is the code in JPA:

@Repository
public interface VoteRepository extends JpaRepository<Vote, Long> {
    @Query("SELECT NEW com.example.polls.model.ChoiceVoteCount(v.choice.id, count(v.id)) FROM Vote v WHERE v.poll.id in :pollIds GROUP BY v.choice.id")
    List<ChoiceVoteCount> countByPollIdInGroupByChoiceId(@Param("pollIds") List<Long> pollIds);

    @Query("SELECT NEW com.example.polls.model.ChoiceVoteCount(v.choice.id, count(v.id)) FROM Vote v WHERE v.poll.id = :pollId GROUP BY v.choice.id")
    List<ChoiceVoteCount> countByPollIdGroupByChoiceId(@Param("pollId") Long pollId);

    @Query("SELECT v FROM Vote v where v.user.id = :userId and v.poll.id in :pollIds")
    List<Vote> findByUserIdAndPollIdIn(@Param("userId") Long userId, @Param("pollIds") List<Long> pollIds);

    @Query("SELECT v FROM Vote v where v.user.id = :userId and v.poll.id = :pollId")
    Vote findByUserIdAndPollId(@Param("userId") Long userId, @Param("pollId") Long pollId);

    @Query("SELECT COUNT(v.id) from Vote v where v.user.id = :userId")
    long countByUserId(@Param("userId") Long userId);

    @Query("SELECT v.poll.id FROM Vote v WHERE v.user.id = :userId")
    Page<Long> findVotedPollIdsByUserId(@Param("userId") Long userId, Pageable pageable);
}

I’m trying to use these myBatis imports instead:

import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;
import org.apache.ibatis.annotations.Param;

Question first asked here

sql – Trazer múltiplos dados

Eu tenho esse select, a intenção dele é ele me retornar os kits que contenham esses ids obrigatoriamente (pode conter mais produtos mas esses precisam estar), (556, 557,534, 558, 1224, 805) e não retornar esses ids (420, 419). Porém com IN, ele está trazendo dados que não contenham os itens obrigatórios.

select distinct 
    
        ctl_produto_lista.cpli_cpro_id_master as "Id Kit",
        cad_produto.cpro_nome as "Kit",
        cad_produto.cpro_descricao as "Descrição"
        
from ctl_produto_lista 

left join cad_produto

where cpli_cpro_id_item in (556, 557,534, 558, 1224, 805)

and cpli_cpro_id_item not in (420, 419);

sql server – Convert the sql sever script to mariadb script

I’m new to MSSQL and mariaDB. I want to convert the sql script from MSSQL to mariaDB of the following below but i can’t figure out.

IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'client')
CREATE USER `client` FOR LOGIN `client` WITH DEFAULT_SCHEMA=`dbo`;
ALTER ROLE `db_client` ADD MEMBER `client`

What is the best way to migrate mssql to mariaDB?

mysql – ¿Es verdad que el SQL es diferente en los SGBD?

Trabajo desde hace algún tiempo con MySQL, y yo entiendo que la mayoría de SGBD trabajan con el lenguaje de SQL, el inconveniente que me surge es cuando leo en algunas partes que en los SGBD la sintaxis del SQL es un poco diferente y varía un poco, eso me confunde porque pienso yo que ya que tienen SQL debería ser igual para todos ¿Es verdad que el SQL es diferente en los SGBD? ¿Me pueden pasar el enlace de alguna página para entender mejor esto por favor?

SQL Server 2017 Enterprise – Database Administrators Stack Exchange

SQL Server 2017 Enterprise – Database Administrators Stack Exchange

sql server – Improve performance searching nvarchar values on varchar column

How about making the columns Unicode and turn on Data Compression, specifically row compression. That has close to none overhead, but you get Unicode compression with it. I.e., you wouldn’t pay much for having it an nvarchar compared to varchar thanks to Unicode compression that comes with row compression.

You could of course also consider the more aggressive page compression (which includes row compression and with that Unicode compression), but now you get into the “is it worth the overhead” discussion.

I very much doubt that UTF-8 play into this, since it only applies to varchar, so you won’t be changing the data type (you’ll just change the encoding on disk by choosing an UTF-8 collation). I.e., you would still get those non-seeks.

Here’s good reading on the topic: https://sqlquantumleap.com/2018/09/28/native-utf-8-support-in-sql-server-2019-savior-false-prophet-or-both/

How does SQL server share data pages?


Your privacy


By clicking “Accept all cookies”, you agree Stack Exchange can store cookies on your device and disclose information in accordance with our Cookie Policy.




sql server – How to estimate the nonclustered index size before its creation?

I created two scenarios and expected to see the same result but the results were noticeably different. I would like to know where I did wrong.

Scenario 1

Create table mytable1 with clustered index and nonclustered index on [OnlineSalesKey] and [DateKey] columns correspondingly. The total number of rows I inserted: 12627608 rows. Pay attention that [OnlineSalesKey] data type is INT:

enter image description here

create clustered index clustered_index_1 on mytable1

create nonclustered index nonclustered_index_1 on mytable1

enter image description here

Scenario 2

Create table mytable1 with clustered index and nonclustered index on [OnlineSalesKey] and [DateKey] columns correspondingly. The total number of rows I inserted: 12627608 rows. Pay attention that [OnlineSalesKey] data type is INT:

enter image description here

create clustered index clustered_index_1_2 on mytable1.2

create nonclustered index nonclustered_index_1_2 on mytable1.2

enter image description here

Expected result:

both nonclustered indexes should have the same size because clustered index column’s data type is INT, so its size is 4 bytes.

Actual result:

enter image description here

Could you please explain why these sizes are so different?

DreamProxies - Cheapest USA Elite Private Proxies 100 Cheap USA Private Proxies Buy 200 Cheap USA Private Proxies 400 Best Private Proxies Cheap 1000 USA Private Proxies 2000 USA Private Proxies 5000 Cheap USA Private Proxies ExtraProxies.com - Buy Cheap Private Proxies Buy 50 Private Proxies Buy 100 Private Proxies Buy 200 Private Proxies Buy 500 Private Proxies Buy 1000 Private Proxies Buy 2000 Private Proxies ProxiesLive.com Proxies-free.com New Proxy Lists Every Day Proxies123.com Best Quality USA Private Proxies